sp_executesql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_executesql

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-04-10 : 13:37:34

I have a string which I want to pass into an IF statement. Here's what I have:

declare @sumclause varchar(4000)
set @sumclause = '(select count(*) from Participants where Weeks_Left<=8 or CDE_ACTV=''24'''

IF cast(@sumclause as int)>0
BEGIN
...
END

I'm first putting it in a string because I'm passing another string into it which can't be seen above.

As you can see, I tried casting it since it had a problem with computing a string as an int, but it still doesn't work. Help?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 14:04:53

declare @sumclause varchar(4000)
set @sumclause = '(select count(*) from Participants where Weeks_Left<=8 or CDE_ACTV=''24'''

should be:

declare @sumclause int
set @sumclause = (select count(*) from Participants where Weeks_Left<=8 or CDE_ACTV='24')

elsasoft.org

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-04-10 : 14:23:16

AFAIK, declaring a SQL query as an int isn't correct.

declare @sumclause varchar(4000)
set @sumclause = '(select count(*) from Participant_Limits_temp where Weeks_Left<=8 and CDE_ACTV=''14'' and (' + @clause + '))'

@clause is something that gets created within my stored procedure and is going to just be a string.

I want the string @sumclause to basically be executed so that it returns a value which will be in this:

IF {value returned from @sumclause} > 0
BEGIN
...
END

So I need functionality which will store the result of a SQL query in a variable which I can then use in an IF statement.

One possible solution I'm working with now is to create a stored procedure which I will pass the string and then return a value which I can use. I'm not sure if this is possible, though.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 14:34:48

did you try what I suggested? Maybe it would help if I renamed the variables:

declare @sum int
set @sum = (select count(*) from Participants where Weeks_Left<=8 or CDE_ACTV='24')

elsasoft.org

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-04-10 : 14:38:28

Yes, I did...

(cropped a little)

declare @sumval as int
set @sumval = (select count(*) from Participant_Limits_temp where CDE_ACTV='14' and ('' + @clause + ''))

I get this error:
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

It doesn't like the way I'm passing @clause, but it looks right to me.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 14:47:17

ah. that's not what you had originally though. you didn't have that dynamic clause bit in there. you tricked me.

well, if you MUST use dynamic sql, you can get the output value out by calling sp_executesql.

give this a read first though: http://sommarskog.se/dynamic_sql.html

elsasoft.org

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-04-10 : 14:57:15

Believe me... "The Curse and Blessings of Dynamic SQL" is definitely what I've taken from this project I'm working on

. My original post said "I'm first putting it in a string because I'm passing another string into it which can't be seen above.", but that might have been vague.

Now, with regard to sq_executesql, I'm having trouble understanding what I need to do. This is what I want to do, but clearly this isn't right:

set @sumval = exec sp_executesql @sumclause

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-10 : 14:57:41

[code]declare @sumclause Nvarchar(4000)
DECLARE @Result INT
set @sumclause = 'select @Result = count(*) from Participants where Weeks_Left<=8 or CDE_ACTV=''24'''

EXEC sp_executesql @sumclause, N'@Result INT OUTPUT', @Result OUTPUT

IF @Result > 0
BEGIN
...
END[/code]

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-04-10 : 15:05:27

EUREKA!!

Thanks so much guys.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-11 : 05:08:28

Why are you using dynamic sql when you dont pass object names as parameters?
Dynamic where can be done in many methods
www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources