November 2, 2003 at 7:42 pm
Hi guys,
I need your help.
Here is an example of what I am doing in my SP.
CREATE PROCEDURE X
(
@a Bigint
)
AS
...
DECLARE @b-2 nvarchar(100)
SELECT @b-2 = 'SELECT a, b FROM c WHERE '
CASE @a
WHEN 'X' THEN 'a = @a'
WHEN 'Y' THEN 'b = @a'
END
EXEC (@B)
It is much more complex than shown, but my problem is this: @b-2 does't recognize the @a.
What do I need to do to get this to work.
Thanks in advance.
November 2, 2003 at 10:16 pm
when 'x' then 'a =''' + @a + ''''
assumign @a is a char.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 3, 2003 at 4:54 pm
shouldn't we be converting @a to a varchar or nvarchar first before we try to add the string? When it ready for final execution then it will need to be convert back to int or implicite convert to int.
if @a is a datetime then you would have to do convert(varchar(12),@a,101) before trying to add it to the string. You could also pick a different date format of your liking.
mom
November 4, 2003 at 7:54 am
use sp_executesql instead of EXECUTE.
Check SQL Books online for details of how to use sp_executesql.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply