January 7, 2004 at 7:49 am
The following is from a stored procedure ...
CREATE PROCEDURE spUPINupdate_NSI(@Profile_ID varchar(5))
AS
DECLARE @cmd varchar(300) --for dynamic SQL
DECLARE @type char(1) --for test of input
DECLARE @desc varchar(40) --for test of input
DECLARE @debug bit --for development
SET @debug = 1
--test input against type
SET @cmd = 'SELECT Profile_Type FROM Profiles WHERE Profile_ID = ' + @Profile_ID + ''
IF @debug = 1
PRINT @cmd
SELECT @type EXEC(@cmd)
IF @Debug = 1
PRINT @type
IF @type <> 'R'
BEGIN
PRINT 'The Profile ID you entered does not correspond to a referring doctor. Please try again.'
RETURN
END
If I enter a Profile_ID that results in a @type of 'P' the If @type <> 'R' does not return TRUE and the procedure does not terminate as I want it to. What is wrong?
Thank you
Jonathan
January 7, 2004 at 7:59 am
The following line in your code
SELECT @type EXEC(@cmd)
is actually 2 commands, not 1 command that assigns a value to @type.
Try changing to something like ....
.....
--test input against type
SELECT @type = Profile_Type FROM Profiles WHERE Profile_ID = @Profile_ID
IF @Debug = 1
PRINT @type
....
Hope this helps.
Once you understand the BITs, all the pieces come together
January 7, 2004 at 8:17 am
Thomas is correct. If this is just an example of something that actually requires dynamic SQL - look at the sp_executesql and use output parameters to get the results.
Guarddata-
January 7, 2004 at 8:58 am
Thanks!
I am rather new at dynamic sql and had it in my head that a parameter cannot be used as an object name (requireing a cmd string to be built and executed). But of course the profile type is not an object name! I made things much more complicated than they nedded to be.
By the way the strange syntax,
SELECT @type EXEC(@cmd) actually worked! The print @type statement printed P but the tests did not work.
Anyway this works perfectly,
--test input against type
SELECT @type = Profile_Type FROM Profiles WHERE Profile_ID = @Profile_ID
IF @Debug = 1
PRINT @type
IF @type <> 'R'
BEGIN
PRINT 'The Profile ID you entered does not correspond to a referring doctor. Please try again.'
RETURN
END
Thanks again.
January 7, 2004 at 9:07 am
In regards to
SELECT @type EXEC(@cmd) actually worked! The print @type statement printed P but the tests did not work.
Once you understand the BITs, all the pieces come together
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy