test problem

Question

Post reply

test problem

Jonathan Matt

SSCarpal Tunnel

Points: 4027
More actions
January 7, 2004 at 7:49 am

#106830

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

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

guarddata SSCertifiable Points: 6583 More actions · Answer 2

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-

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 3

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.

ThomasH SSCertifiable Points: 7437 More actions · Answer 4

In regards to

SELECT @type EXEC(@cmd) actually worked! The print @type statement printed P but the tests did not work.

"SELECT @type" should return a one column record set with NULL

then

"EXEC (@cmd)" performed the dynamic SQL.

I do not see how @type could have produced a "P" value

unless if was set prior.

Once you understand the BITs, all the pieces come together