August 29, 2006 at 3:10 pm
I am trying to run this simple string as query but it doesnt work...please help..
####################################################################
declare @LoginName varchar(100)
set @LoginName = 'test_sales'
declare @dbname varchar(100)
set @dbname = 'LL_test'
declare @queryString varchar(1000)
Set @queryString = ''
/* 1. Get Access level and Employee No. */
declare @AccessLevelID int
declare @EmployeeNo int
DECLARE @ROWCNT INT
SET @ROWCNT = 0
--start building query string...
set @queryString = 'select @EmployeeNo = b.employee_no, @AccessLevelID = a.Access_Seq from ' + @dbName + '..employee a, ' + @dbName + '..userlog b where a.employee_no = b.employee_no and b.uname = ' + @LoginName
print @queryString --for debugging
print 'step 1'
exec (@queryString) --exec query
SET @rowCnt = @@ROWCOUNT
PRINT @ROWCNT
##########################################################################
I am getting this output. You can copy to QA and see it for urself.....
select @EmployeeNo = b.employee_no, @AccessLevelID = a.Access_Seq from LL_Reliance..employee a, LL_Reliance..userlog b where a.employee_no = b.employee_no and b.uname = test_sales --> this is from print @querystring
step 1 --> this is by print 'step 1'
Server: Msg 137, Level 15, State 1, Line 1 --> this by exec(@queryString)
Must declare the variable '@EmployeeNo'.
0 --> Print @Rowcnt
I am not sure what the error is since @EmployeeNo is declared. Its surely because SQL Server doesnt know how to interpret the string because by the time the query is about to execute it doesnt know that it should store results in @EmployeeNo & AccessLevelID ..
plz advise..this is driving me crazy
August 29, 2006 at 3:15 pm
Try this:
SET @queryString = 'SELECT ' + @EmployeeNo + ' = b.employee_no, @AccessLevelID = a.Access_Seq FROM ' +
@dbName + '..employee a, ' + @dbName + '..userlog b WHERE a.employee_no = b.employee_no
AND b.uname = ' + @LoginName
I wasn't born stupid - I had to study.
August 29, 2006 at 3:19 pm
wont work because @EmployeeNo is int and so is @AccessLevelID.
Server: Msg 245, Level 16, State 1, Line 17
Syntax error converting the varchar value 'SELECT ' to a column of data type int.
August 29, 2006 at 3:44 pm
try this
SET @queryString = 'SELECT b.employee_no, a.Access_Seq FROM ' +
rtrim(convert(char,@dbName)) + '..employee a, ' + rtrim(convert(char,@dbName)) + '..userlog b WHERE a.employee_no = b.employee_no
AND b.uname = ''' + @LoginName+''''
this will work..
The thing you are doing is.. in dynamic sql if you use local variables, you cant get them after that statement. because scope problem ..the declared variables doesn't exist after that scope
August 30, 2006 at 7:01 am
Yeah but the whole idea is to store the 2 col's returned by SELECT in the variables... i guess this will not work. I should look in the direction of creating temp table and storing values in that select clause... that way I dont have to use those 2 variables..
I agree ur query will work but will not take care of var's which are needed.
thanks again.
August 30, 2006 at 7:26 am
Try sp_executesql with output parameters. Something like:
declare @EmployeeNo int, @AccessLevelID int, @err int
set @queryString = 'select @EmployeeNo = b.employee_no, @AccessLevelID = a.Access_Seq from '
+ @dbName + '..employee a, '
+ @dbName + '..userlog b where a.employee_no = b.employee_no and b.uname = @LoginName'
exec @err = sp_executesql @queryString
,N'@EmployeeNo int output, @AccessLevelID int output, @LoginName varchar(50)'
,@EmployeeNo output
,@AccessLevelID output
,@LoginName
select @EmployeeNo, @AccessLevelID
August 30, 2006 at 10:38 am
Yes, Using Output paremeters it should work
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply