Stored Procedure

  • Thread starter Thread starter Dan M
  • Start date Start date
D

Dan M

Can't get my stored procedure to return the value I want.
When I hard code the criteria of the WHERE statement, all
is fine. When I use a variable, it returns nothing.

The Sproc looks like this:
DECLARE @theUser nvarchar(12)
SET @theUser = suser_sname()
SELECT UserRegion FROM tblCurrentUserSettings WHERE UserID
= @theUser

If I use this SELECT statement, replacing the variable
with a hard coded value, it works:
SELECT UserRegion FROM tblCurrentUserSettings WHERE UserID
= 'cti/mccleldr'

I test the value of @theUser, and it returns cti/mccleldr

I assume maybe the datatype of the suser_sname() function
is the problem, so I added another variable and tried:
SET @theUserConverted = Cast(@theUser AS nvarchar(12))

This new variable returns the same value I need, but still
doesn't work in the SELECT statement.

I even tried using a string variable to concatenate the
SELECT statement, it still returns nothing.
SET @mySQL = "SELECT UserRegion FROM
tblCurrentUserSettings WHERE UserID = " + @theUser
Exec (@mySQL)

I've tried everything I can think of. Each step works
properly by itself, but putting the steps together will
never return the value I need. Auuugh. Help.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Work from the query analyzer on just the SELECT statement & variable
declarations.

I'm assuming you've checked the value of @mySQL to see if the @theUser
value is included in it? Print @mySQL will show the variable value.

You may also wish to try this:

SELECT UserRegion FROM tblCurrentUserSettings
WHERE UserID = SUSER_NAME()

You don't have to put the return value of the SUSER_NAME() function in
a variable - you can use the function directly in the SQL string.

You may also wish to think about using the function USER_NAME()
instead of SUSER_NAME(). When signed on as db owner (dbo)
SUSER_NAME() returns a NULL.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDUhK4echKqOuFEgEQJWawCgsA/xpv5t862cwIIZyTNvdJlQjRkAnRmX
xmTB9CGPkC9+2bv0FGjKORQj
=O7Vc
-----END PGP SIGNATURE-----
 
Back
Top