Problem Passing Variable to SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem Passing Variable to SP

Author

Topic

Silenz
Starting Member

7 Posts

Posted - 2005-08-18 : 12:49:09

I am having problems with the below code. I need to be able to pass the @Server variable into the OpenQuery(@Server, ... ). However, I get an error stating I need to declare the variable. Of course, it is already declared and works in the earlier portion of the code. The code works if I hard code the server name in the OpenQuery(ActualName, ... ) however I really would like to pass the variable.

Create Procedure UnCrosstbl
@table varchar(4),
@Server varchar(50)
AS
DECLARE @sql varchar(8000)
SELECT @sql = ''
SELECT @sql = @sql + 'SELECT ''' + @table + ''' as TBL_ID, Eval_id, ''' + Right(column_name,4) + ''' AS Ques_Nmbr,
Convert(varchar(250),' + column_name + ') AS Answer FROM ' + @Server + '.Witness.DBO.'+ table_name + ' UNION '
FROM OpenQuery (@Server, 'Select * from information_schema.columns')
WHERE table_name = @table AND column_name <> 'Eval_id'
SELECT @sql = LEFT(@sql, Len(@sql) - 5) EXEC (@sql)

GO

By the way thanks for this nice code to start with.

SQLTEAMSteve
Starting Member

8 Posts

Posted - 2005-08-18 : 13:21:47

You need to make it so that when the final "print @SQL" call prints, it displays the server name, table names, etc, NOT the variable. For example, copy + paste the below code I've altered for you and run it in QA. See how the statement prints out? Once you get @SQL equal to what it needs to be, call up exec sp_executeSQL @SQL and you're good to go. Also, I like to try and figure out what my base query is, both on the data source server AND in openQuery format. Hope this helps!

DECLARE @sql nvarchar(4000), @table varchar(10), @server varchar(10), @column_name varchar(10)
set @server='TESTSQL'
set @table='TESTTABLE'
set @column_name='MYCOLUMN'
SET @sql =( 'SELECT ' + @table + ' as TBL_ID, Eval_id, ''' + Right(@column_name,4) + ''' AS Ques_Nmbr,
Convert(varchar(250),' + @column_name + ') AS Answer FROM ' + @Server + '.Witness.DBO.'+ @table + ' UNION
FROM OpenQuery (' + @Server + ', ''Select * from information_schema.columns'')')

select @sql

--And the below results

SELECT TESTTABLE as TBL_ID, Eval_id, 'LUMN' AS Ques_Nmbr,
Convert(varchar(250),MYCOLUMN) AS Answer FROM TESTSQL.Witness.DBO.TESTTABLE UNION
FROM OpenQuery (TESTSQL, 'Select * from information_schema.columns')

_________________________
Stephen R Montgomery

SQLTEAMSteve
Starting Member

8 Posts

Posted - 2005-08-18 : 13:24:12

Also, one more thing:
A: you must always use the datatype nvarchar or nchar to execute it in sp_executeSQL

_________________________
Stephen R Montgomery

Silenz
Starting Member

7 Posts

Posted - 2005-08-18 : 13:48:13

Unfortunately, it does not look like the above code will work for my purpose. The original code is selecting the Column name dynamically from the information schema.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources