June 16, 2009 at 11:54 am
I have an sp that works fine. However, I need to parameterize a server name in a SELECT statement in the sp. Currently, I have this line of code:
IF NOT EXISTS(SELECT * FROM nydev32.TRACKIT8_DATA.DBO.Tasks WHERE UserID = @user-id AND Task LIKE '%Secretarial Assignment%' AND ClsdDate IS NULL)
@user-id is an int variable declared earlier. Its value is also set earlier.
What I need to do is parameterize the server name ("nydev32"). So I've replaced the line of code above with the following:
DECLARE @StrSelect as nvarchar(2000)
DECLARE @CountRows as int
SET @StrSelect = N'SELECT COUNT(*) FROM ' + @TrackitServer + '.TRACKIT8_DATA.DBO.Tasks WHERE UserID = ' + CAST(@UserID AS varchar(5)) + ' AND Task LIKE ' + '''' + '%Secretarial Assignment%' + '''' + ' AND ClsdDate IS NULL'
EXEC sp_executesql @StrSelect, N'@CountRows int OUTPUT', @CountRows OUTPUT
IF @CountRows = 0
June 16, 2009 at 11:57 am
OK, sorry, somehow I posted this before I'd finished writing it. This is the 2nd time I've done that on this forum recently. Sorry!!! :hehe:
Anyway, as I was saying....
My new code works fine as far as setting @CountRows correctly. I've been able to test that with a PRINT command. HOWEVER, the IF statement
IF @CountRows = 0
does not return true even when @CountRows really is 0.
Is this a scope problem? What am I missing here?
Thanks!
June 16, 2009 at 12:13 pm
Try changing this:
SET @StrSelect = N'SELECT COUNT(*) FROM ' + @TrackitServer + '.TRACKIT8_DATA.DBO.Tasks WHERE UserID = ' + CAST(@UserID AS varchar(5)) + ' AND Task LIKE ' + '''' + '%Secretarial Assignment%' + '''' + ' AND ClsdDate IS NULL'
to this:
SET @StrSelect = N'SELECT @CountRows = (SELECT COUNT(*) FROM ' + @TrackitServer + '.TRACKIT8_DATA.DBO.Tasks WHERE UserID = ' + CAST(@UserID AS varchar(5)) + ' AND Task LIKE ' + '''' + '%Secretarial Assignment%' + '''' + ' AND ClsdDate IS NULL)'
See if that does what you need. You'll probably need to change the name of the output parameter to something different than the variable declared in the calling proc. Test that if you get an error.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2009 at 12:29 pm
That was it! Thanks so much! 😀
June 16, 2009 at 12:30 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply