March 3, 2010 at 3:43 am
Can any one please tell me how to call a stored procedure from case statement.
March 3, 2010 at 4:11 am
with a CASE statement, you can't. case statements return data or help filter a WHERE statement.
a case helps goes something like this:
SELECT
CASE
WHEN status=1
THEN 'Open'
WHEN status=2
THEN 'Closed'
END
FROM YOURTABLE
you can do it with an IF statement, however. an IF statement can help you control logic flow.
if @param = 1
begin
exec sp_who
end
elseif @param = 2
begin
exec sp_who2
end
Lowell
March 3, 2010 at 4:18 am
Thanks for the reply, my current problem looks some thing like below
I need to update all the records in a table based on specific contion like below psudecode
declare @test-2 datetime
set @test-2 = GETDATE()
Update <Table1>
Set ResultDate = CASE WHEN(DateName(dw, @test-2)='Sunday')
THEN DATEADD(DD,-1, @test-2)
ELSE <<Execute SP>> END
FROM Table1 inner join Table2 ......
March 3, 2010 at 4:19 am
I do not think procedure call is allowed in the condition or the result expression of the case statement.
If somebody can provide the information, it would be great.
March 3, 2010 at 4:28 am
if you have two logical steps, you'll need to update statements, each witha WHERE statement to control which rows get updated.
what does the stored procedure do?
your pseudo code doesn't have a WHERE statement, so how do you know what data will get the new data?
explain exactly what you are trying to do...help us help you;
i read your example and i came out with at least two things you want to do. show us the cREATE TABLE statements for Table1 and Table2, and how they join together.
--if today is Sunday, set every record in the database to Saturday? nothing happens if today is NOT Sunday
UPDATE MYTABLE
SET RESULTDATE = DATEADD(DD,-1, @test-2)
WHERE (DateName(dw, @test-2)='Sunday')
--if the current value in the db is a Sunday, change it to the Saturday /day before.
UPDATE MYTABLE
SET RESULTDATE = DATEADD(DD,-1, RESULTDATE )
WHERE (DateName(dw, RESULTDATE )='Sunday')
vamsy_mohan007 (3/3/2010)
Thanks for the reply, my current problem looks some thing like belowI need to update all the records in a table based on specific contion like below psudecode
declare @test-2 datetime
set @test-2 = GETDATE()
Update <Table1>
Set ResultDate = CASE WHEN(DateName(dw, @test-2)='Sunday')
THEN DATEADD(DD,-1, @test-2)
ELSE <<Execute SP>> END
FROM Table1 inner join Table2 ......
Lowell
March 3, 2010 at 4:50 am
Below is sample what I am looking for
create table TABLE1
(
IDVAL INT,
TEST DATETIME,
CHANNEL INT
)
create table TABLE2
(
IDVAL INT,
CHANNEL INT
)
INSERT INTO TABLE1 VALUES('1',GETDATE(),2)
INSERT INTO TABLE1 VALUES('2',GETDATE(),2)
INSERT INTO TABLE1 VALUES('3',GETDATE(),2)
INSERT INTO TABLE2 VALUES('1',2)
INSERT INTO TABLE2 VALUES('2',5)
INSERT INTO TABLE2 VALUES('3',2)
/*existing sp has lot of logic involved and returns the value*/
CREATE PROCEDURE TESTPROC
AS
BEGIN
SELECT DATEADD(DD,-2, getdate())
END
Update TABLE1
Set TEST = CASE WHEN(DateName(dw, GETDATE())='Sunday')
THEN DATEADD(DD,-1, GETDATE())
ELSE TESTPROC END
FROM TABLE1 T1 inner join TABLE2 T2 ON T1.IDVAL = T2.IDVAL AND T1.CHANNEL=T2.CHANNEL
RESULT WHICH I AM TRYING IS BOTH THE MATCHING RECORDS (3(IDVAL),2(CHANNEL)) should be updated but i recieve the below error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'TESTPROC'.
March 3, 2010 at 4:57 am
ok great that helps a lot...i can do the table and the joins.
just need one more piece of information.
what do you want to do to RESULTDATE? i thought you either want to set it to the matching Saturday, or update them to all the same date...both of thouse ideas are probably wrong.
don't try to code it, if you can explain it, we can help.
Lowell
March 3, 2010 at 5:05 am
ok, here's some working code, witht eh update you seem to need; but here's the rub:
this is what it does:
for each matching record, if today happens to be sunday, then it sets the date to saturday.
if it's not sunday, it sets the date to today minus two days.
is that what you wanted to do?
create table TABLE1
(
IDVAL INT,
TEST DATETIME,
CHANNEL INT
)
create table TABLE2
(
IDVAL INT,
CHANNEL INT
)
INSERT INTO TABLE1 VALUES('1',GETDATE(),2)
INSERT INTO TABLE1 VALUES('2',GETDATE(),2)
INSERT INTO TABLE1 VALUES('3',GETDATE(),2)
INSERT INTO TABLE2 VALUES('1',2)
INSERT INTO TABLE2 VALUES('2',5)
INSERT INTO TABLE2 VALUES('3',2)
/*existing sp has lot of logic involved and returns the value*/
Update T1 --table1 alias
Set TEST = CASE
WHEN(DateName(dw, GETDATE())='Sunday')
THEN DATEADD(DD,-1, GETDATE())
ELSE DATEADD(DD,-2, getdate()) --do the logic of the proc, not call the proc
END
FROM TABLE1 T1
inner join TABLE2 T2
ON T1.IDVAL = T2.IDVAL
AND T1.CHANNEL=T2.CHANNEL
Lowell
March 3, 2010 at 5:13 am
The SP contains more than 500 lines of code and specific logic like manipulation of data from more than 3 tables etc...
so we cannot move the logic completely to case statement. hope that answers your question. Thanks for your help
March 3, 2010 at 5:15 am
we need to get the date from the sp and just set the date to all the records in TABLE 1 matching records
March 3, 2010 at 5:22 am
yeah pseudo code is bad. your placeholder proc implied something completely different.
it is absolutely not possible to call a procedure with an update statement.
you can call a function, which returns a value, but not a stored procedure that does it's own updates.
does your proc accept parameters? again, what is it doing? updating a table based on a parameter? fine, just create a cursor that finds the right records and call your proc for each value.
you seem to avoid stating what the proc does every time i ask, so maybe you should sit with the person who wrote the proc; he'd probably have some better ideas on how to call it.
Lowell
March 3, 2010 at 5:52 am
Thank you, Will try using the function instead of SP but to clarify the
has its own custom logic to arrive the result.
March 4, 2010 at 3:01 am
oh i see
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply