• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • Last Modified:

t-sql dynamic sql execute twice with different column names

I have a dynamic sql statement that I need to execute twice with different column names.  How can I do this:
declare @cols as nvarchar(50)
declare @col_name as nvarchar(50)
declare @tables as nvarchar(50)
declare @sql as nvarchar(300)

set @cols = 'Name'--original column name
set @tables = 'Bank'
set @col_name = 'NAME123'--  display column name

set @sql = N'SELECT '+ @cols +' as '+@col_name+'  FROM ' + @tables 
EXECUTE sp_executesql @sql

set @col_name = 'NAME456'--  display column name
EXECUTE sp_executesql @sql

Open in new window

0
maverick0728
Asked:
maverick0728
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You'll need to set again the @sql string for the second call:
--  2nd call
set @col_name = 'NAME456'
set @sql = N'SELECT '+ @cols +' as '+@col_name+'  FROM ' + @tables 
EXECUTE sp_executesql @sql

Open in new window

0
 
Nitin SontakkeDeveloperCommented:
Preferably encapsulating within square brackets while constructing a string itself.
0
 
Mark WillsTopic AdvisorCommented:
Well, unless you want to capture results in between executes, you could do
declare @cols as nvarchar(50)
declare @col_name as nvarchar(50)
declare @tables as nvarchar(50)
declare @sql as nvarchar(300)

set @cols = 'Name'--original column name
set @tables = 'Bank'
set @col_name = 'NAME123'--  display column name

set @sql = N'SELECT '+ @cols +' as '+@col_name+'  FROM ' + @tables 

set @col_name = 'NAME456'--  display column name

set @sql = @sql + N'
SELECT '+ @cols +' as '+@col_name+'  FROM ' + @tables 

EXECUTE sp_executesql @sql

Open in new window

0
 
maverick0728Author Commented:
I only want to declare the @sql once and feed it the first column name, execute, then feed it the second column name and execute it again.  Maybe dynamic sql isn't the way to go here.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
But it's declared only once. Only thing is that you'll need to set the variable value every time you'll need to run a different SELECT.

Other option is using a stored procedure:
CREATE PROCEDURE MySQLExecute (@cols nvarchar(50), @col_name nvarchar(50), @tables nvarchar(50))
AS
	declare @sql as nvarchar(300)

	set @sql = N'SELECT '+ @cols +' as '+@col_name+'  FROM ' + @tables 
	EXECUTE sp_executesql @sql
GO

Open in new window

After you creating the SP, by running the above code, you just need to call it like the following:
declare @cols as nvarchar(50)
declare @col_name as nvarchar(50)
declare @tables as nvarchar(50)

set @cols = 'Name'--original column name
set @tables = 'Bank'
set @col_name = 'NAME123'--  display column name
EXEC MySQLExecute @cols, @col_name, @tables

--2nd call
set @col_name = 'NAME456'--  display column name
EXEC MySQLExecute @cols, @col_name, @tables

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now