Wednesday, April 18, 2012 1:09 PM
I am using sql server 2000 and I have a dynamic query and needs to store 2 results in a variable. My dynamic query is as follows
SET @dynamic = N'SELECT @ID = ID, @NAME = NAME, FROM '+@dbname+' (NOLOCK) WHERE accountno = 11111'
The query will always return only on row. I want to get both the results in the variable. This is working fine if I get one variable but its not working if I get 2 variables.
FYI - the output to retrieve one output I am using is as - EXEC sp_executesql @dynamic ,N'@ID float OUTPUT' ,@ID OUTPUT
I read in a forum that we can achieve this by inserting into temp table but want to know if there is any other way. Please post me the code to achieve this.
Wednesday, April 18, 2012 1:14 PM
Try using the syntax in example 1:
DECLARE @dbname varchar(20) DECLARE @dynamic NVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500) DECLARE @ID_Val int DECLARE @NAME_Val varchar(50) SET @dbname = 'DatabaseName' SET @dynamic= N'SELECT @ID = ID, @NAME = NAME, FROM '+@dbname+' (NOLOCK) WHERE accountno = 11111' SET @ParmDefinition = N'@ID int OUTPUT, @NAME varchar(50) OUTPUT' EXECUTE sp_executesql @SQLString, @ParmDefinition, @ID=@ID_Val OUTPUT, @NAME=@NAME_Val OUTPUT SELECT @ID_Val, @NAME_Val
Wednesday, April 18, 2012 1:21 PMModerator
What is the problem with
EXEC sp_executesql @dynamic ,N'@ID float OUTPUT, @Name varchar(20) OUTPUT' ,@ID OUTPUT, @Name OUTPUT
For every expert, there is an equal and opposite expert. - Becker's Law
- Marked As Answer by Iam_Rakesh Thursday, April 19, 2012 5:14 AM
Thursday, April 19, 2012 1:55 AM>> I am using SQL Server 2000 and I have a dynamic query and needs to store 2 results in a variable. <<
Dynamic SQL is how you tell the world that your programming ability is so bad that any random user in the future can do a better job at run time than you can with full knowledge of the data model.
But then you compound your shame by showing total ignorance of RDBMS! The relational model is based ton tables; the columns of tables are scalar values. This axiom leads to the Normal Forms and everything in the relational model.
As someone who helped write the standards for SQL, please stop programming until you have a minimal education. You are doing everything wrong.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
Thursday, April 19, 2012 5:16 AM
The solution that u suggested is working fine. Many thanks for that. I have one more doubt. if the ID returns as NULL, then I am getting the below error
Warning: Null value is eliminated by an aggregate or other SET operation.
Could you please tell me how this can be avoided?
Thursday, April 19, 2012 5:38 AM
I tried ISNULL(id,0) and its working. Thanks anyways.
- Marked As Answer by Iam_Rakesh Thursday, April 19, 2012 5:38 AM