Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Authors
About us
Contact us
Newsletters
Write for us
Daily SQL Articles by email:
Sign up
Back
SQLServerCentral
Register
Home
»
Programming
»
General
»
Call sp from a Case statement
Call sp from a Case statement
Post reply
Like
110
Add to Briefcase
Call sp from a Case statement
View
Options
Author
Message
Vitor da Fonseca
Vitor da Fonseca
Posted 2 years ago
#1910810
SSC Veteran
Group: General Forum Members
Points: 279
Visits: 53
Hi,
I am trying to execute a Stored Procedure after two other jobs in the schedule have completed
successfully, so I am looking to a specific filed (DW_CREATED_DATE) which is the date that the data loaded.
This is what I have done, but it seems that the EXECUTE do not work within the CASE statement, so I am looking for help on the solution.
[
code
language="
sql
"]
SELECT
CASE
WHEN cast(A.[DW_CREATED_DATE] as date) = '2017-12-03' AND cast(B.[DW_CREATED_DATE] as date) = '2017-12-03' THEN EXEC sp_ChurnRate
else NULL end
FROM TABLE_A A
INNER JOIN TABLE_B B ON CAST(B.[DW_CREATED_DATE] AS DATE) = CAST(A.[DW_CREATED_DATE] AS DATE)
[
/code]
When running the above, I get the error message:
<< Incorrect syntax near the keyword 'EXEC'.
>>
Any help and all help is very much appreciated.
Thanks in advance,
All help and Any help
is appreciated
11
Quote
Thom A
Thom A
Posted 2 years ago
#1910827
SSC Guru
Group: General Forum Members
Points: 92489
Visits: 23209
What are you trying to do here exactly??? You can't execute a stored procedure inside a SELECT statement. At a pure guess, maybe what you're after is a (Table Value) function, however, without knowing what sp_ChurnRate does, and how it related to the rest of your statement, that;s about all the advice I can give.
You'd be better off providing the SQL for sp_ChurnRate, which would be a start. We may then also need DDL for your Table, as well as consumable sample data and your expected output.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P
Please always remember to encapsulate your code in IFCode Markup. For example
[code=sql] [/code]
.
Click here
to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
11
Quote
Vitor da Fonseca
Vitor da Fonseca
Posted 2 years ago
#1910851
SSC Veteran
Group: General Forum Members
Points: 279
Visits: 53
Hi, Many thanks for your reply. What I'm trying to do is running a script/code which I put in a ‘Stored Procedure’ after the other two jobs have completed
successfully
This is the stored procedure, which delete all records from the table and then insert the most recent information in the table
[
code
language="
sql
"]
AS
DELETE FROM [Imart]
.
[
ChurnRate
]
INSERT INTO [Imart]
.
[
ChurnRate
]
SELECT
BASE.SUBS_WEEK_AND_YEAR, DAY_ID, DATE, CHURN.BB_PACKAGE ,CHURN.DTH_CUSTOMER_TYPE, CURRWK, LASTWK, TOTAL_CHURN
FROM
(
select
subs_week_and_Year, Day_ID, cast(CALENDAR_DATE as date) date, BB_PACKAGE_DESC, sum(CURR_WEEK) currwk, sum(LAST_WEEK) LASTWK,DTH_CUSTOMER_TYPE
from
dbo
.
Base
group
by
subs_week_and_Year, Day_ID, cast
(
CALENDAR_DATE as date), BB_PACKAGE_DESC, DTH_CUSTOMER_TYPE
) BASE
left
JOIN
(SELECT
SUBS_WEEK_AND_YEAR, BB_PACKAGE, DTH_CUSTOMER_TYPE, SUM(TOTAL_RECORDS) TOTAL_CHURN
FROM Imart
.
BB_Churn
GROUP BY
SUBS_WEEK_AND_YEAR, BB_PACKAGE, DTH_CUSTOMER_TYPE
) CHURN ON CHURN
.
SUBS_WEEK_AND_YEAR = BASE
.
SUBS_WEEK_AND_YEAR
AND CHURN
.
BB_PACKAGE = BASE
.
BB_PACKAGE_DESC
and
churn
.
DTH_CUSTOMER_TYPE = base
.
DTH_CUSTOMER_TYPE
ORDER BY
BASE
.
subs_week_and_Year ASC
;
[
/code]
Again, many thanks for the help
.
All help and Any help
is appreciated
11
Quote
Thom A
Thom A
Posted 2 years ago
#1910861
SSC Guru
Group: General Forum Members
Points: 92489
Visits: 23209
Wait... That SP is only doing deletes and inserts..? What would you be expecting your unnamed column to be returning in your select statement, considering that your SP isn't returning any data.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P
Please always remember to encapsulate your code in IFCode Markup. For example
[code=sql] [/code]
.
Click here
to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
11
Quote
anthony.green
anthony.green
Posted 2 years ago
#1910869
SSC Guru
Group: General Forum Members
Points: 107090
Visits: 8698
+
x
Thom A - Monday, December 4, 2017 6:22 AM
Wait... That SP is only doing deletes and inserts..? What would you be expecting your unnamed column to be returning in your select statement, considering that your SP isn't returning any data.
Think this is more workflow, so instead of SELECT CASE should be an IF statement.
+
x
Vitor da Fonseca - Monday, December 4, 2017 6:08 AM
Hi, Many thanks for your reply. What I'm trying to do is running a script/code which I put in a ‘Stored Procedure’ after the other two jobs have completed
successfully
What is the logic behind the two jobs, how do they execute, is it via SQL Agent, windows task scheduler or some other methods?
If they are automated jobs like the SQL Agent, just add another step and do an IF check
IF This = That
BEGIN
EXEC sp_churnrate
END
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger
14
Quote
Vitor da Fonseca
Vitor da Fonseca
Posted 2 years ago
#1910873
SSC Veteran
Group: General Forum Members
Points: 279
Visits: 53
The SP delete all records from the table "Imart
.
ChurnRate
" and then inserts information from two other different tables with these column names:
BASE.SUBS_WEEK_AND_YEAR, DAY_ID, DATE, CHURN.BB_PACKAGE ,CHURN.DTH_CUSTOMER_TYPE, CURRWK, LASTWK, TOTAL_CHURN
What I am trying to do is instead of guessing a time that the first two jobs are completed
successfully, is to have a job which
run
the SQL to build the table Imart
.
ChurnRate
.
The first two jobs when they run, the column DW_CREATED_DATE is added
with
the date that it
run
, i.e.
when
they
have run
today the column will be populated for all records with today's date (04-12-2017), this also serves as a check, and
off
course if for any reason something went wrong I am able to run the query again for the date required.
I hope that I've managed to explain, and please I
apologise
if my English is not the most correct...
Thanks in advance
,
All help and Any help
is appreciated
9
Quote
anthony.green
anthony.green
Posted 2 years ago
#1910876
SSC Guru
Group: General Forum Members
Points: 107090
Visits: 8698
+
x
Vitor da Fonseca - Monday, December 4, 2017 6:45 AM
The SP delete all records from the table "Imart
.
ChurnRate
" and then inserts information from two other different tables with these column names:
BASE.SUBS_WEEK_AND_YEAR, DAY_ID, DATE, CHURN.BB_PACKAGE ,CHURN.DTH_CUSTOMER_TYPE, CURRWK, LASTWK, TOTAL_CHURN
What I am trying to do is instead of guessing a time that the first two jobs are completed
successfully, is to have a job which
run
the SQL to build the table Imart
.
ChurnRate
.
The first two jobs when they run, the column DW_CREATED_DATE is added
with
the date that it
run
, i.e.
when
they
have run
today the column will be populated for all records with today's date (04-12-2017), this also serves as a check, and
off
course if for any reason something went wrong I am able to run the query again for the date required.
I hope that I've managed to explain, and please I
apologise
if my English is not the most correct...
Thanks in advance
,
If its a Job, add a 3rd step to run the stored proc
So the logic would be
Step1 - Populate Base Table, on success go to next step, on failure quit report failure
Step 2 - Populate BB_Churn, on success go to next, on failure quit report failure
Step 3 - Execute sp_churnrate, on success quit report success, on failure quit report failure
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger
11
Quote
Vitor da Fonseca
Vitor da Fonseca
Posted 2 years ago
#1910878
SSC Veteran
Group: General Forum Members
Points: 279
Visits: 53
Not as simple as that, sorry
Two distinct jobs which one with different steps, that is why I am having
difficulties
:
crying:
All help and Any help
is appreciated
15
Quote
anthony.green
anthony.green
Posted 2 years ago
#1910882
SSC Guru
Group: General Forum Members
Points: 107090
Visits: 8698
+
x
Vitor da Fonseca - Monday, December 4, 2017 7:01 AM
Not as simple as that, sorry
Two distinct jobs which one with different steps, that is why I am having
difficulties
:
crying:
Why not get the jobs to call each other, End of Job 1 Start Job 2, End of Job 2 Start Job 3
Create a logic table, end of Job 1 Populate a table with a row of value 1. end of Job 2 Populate a second row with value 1. job 3 running always, if sum(value) = 2 exec sp_churnrate reset the values to 0. rinse and repeat or go with IF TableA.Date = CONVERT(DATE,GETDATE()) AND TableB.Date = CONVERT(DATE,GETDATE()) EXEC sp_churnrate.
Problem with the latter option if the job is constantly running to check if the values match then the stored proc will always be running.
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger
11
Quote
Vitor da Fonseca
Vitor da Fonseca
Posted 2 years ago
#1910896
SSC Veteran
Group: General Forum Members
Points: 279
Visits: 53
+
x
[
quote]
anthony.green - Monday, December 4, 2017 7:08 AM
+
x
[
quote]
Vitor da Fonseca - Monday, December 4, 2017 7:01 AM
Not as simple as that, sorry
Two distinct jobs which one with different steps, that is why I am having
difficulties
:
crying:
[/
quote
]
Why not get the jobs to call each other, End of Job 1 Start Job 2, End of Job 2 Start Job
3
Create a logic table, end of Job 1 Populate a table with a row of value 1.
end
of Job 2 Populate a second row with value 1.
job
3 running always, if sum
(
value) = 2 exec sp_churnrate reset the values to 0.
rinse
and repeat or go with IF
TableA
.
Date = CONVERT
(
DATE
,
GETDATE
(
)) AND TableB
.
Date = CONVERT
(
DATE
,
GETDATE
(
)) EXEC sp_churnrate.
Problem
with the latter option if the job is constantly running to check if the values
match then
the stored
proc
will always be running.
[/
quote
]
Great, Many thanks,
I will try to build something with your first solution, I hope I can do it
, still a newbie when it comes to SQL
All help and Any help
is appreciated
6
Quote
Go
Post reply
Like
110
Add to Briefcase
Post quoted reply
Permissions
You
can't
post new topics.
You
can't
post topic replies.
You
can't
post new polls.
You
can't
post replies to polls.
You
can't
edit your own topics.
You
can't
delete your own topics.
You
can't
edit other topics.
You
can't
delete other topics.
You
can't
edit your own posts.
You
can't
edit other posts.
You
can't
delete your own posts.
You
can't
delete other posts.
You
can't
post events.
You
can't
edit your own events.
You
can't
edit other events.
You
can't
delete your own events.
You
can't
delete other events.
You
can't
send private messages.
You
can't
send emails.
You
can
read topics.
You
can't
vote in polls.
You
can't
upload attachments.
You
can
download attachments.
You
can't
post HTML code.
You
can't
edit HTML code.
You
can't
post IFCode.
You
can't
post JavaScript.
You
can
post emoticons.
You
can't
post or upload images.
Select a forum
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Azure Data Factory
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
SQL Server Security Skills
Question of the Day (QOD)
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Sonasoft
Golden Gate Software
Lumigent
Red Gate Software
Quest Software
ApexSQL
Idera
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss T-SQL Books
Discuss Data Warehousing Books
Discuss DTS Books
Discuss SQL Server 7.0 Books
Discuss SQL Server 2000 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
Narrow your search by forum
Explore
Home
Latest
Popular
Calendar
Members
Who's on
Moderators
Forums
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Azure Data Factory
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
SQL Server Security Skills
Question of the Day (QOD)
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Sonasoft
Golden Gate Software
Lumigent
Red Gate Software
Quest Software
ApexSQL
Idera
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss T-SQL Books
Discuss Data Warehousing Books
Discuss DTS Books
Discuss SQL Server 7.0 Books
Discuss SQL Server 2000 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
SQLServerCentral
Register
Search
Narrow your search by forum
Unthreaded, ascending
Unthreaded, descending
Subscribe to topic
Print topic
RSS feed
Go to topics forum
Jump to page
Jump to page
Copyright © 2002-2019 Redgate. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.