October 4, 2009 at 11:57 pm
I am facing a weired problem while using 'sp_executesql' to execute the dynamic queries inside the Stored Procedures:
Here is the sample code
declare @cols as nvarchar(50)
declare @tables as nvarchar(50)
DECLARE @ParmDefinition NVARCHAR(200)
declare @sql as nvarchar(300)
set @cols = 'Name'
set @tables = 'Bank'
set @sql = N'SELECT @cols FROM ' + @tables
SET @ParmDefinition = N'@cols VARCHAR(50)'
EXECUTE sp_executesql
@sql,
@ParmDefinition,
@cols='Name'
Here I am passing the column name as a parameter, But, as an Output i m getting the column name 'Name' instead of getting the Column contents. Well, I know that this is just a preventive measure of not to add any SQL from outside but from the parameter.
Is there any work around of this problem?
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 5, 2009 at 12:11 am
declare @cols as nvarchar(50)
declare @tables as nvarchar(50)
declare @sql as nvarchar(300)
set @cols = 'Name'
set @tables = 'Bank'
set @sql = N'SELECT '+ @cols +' FROM ' + @tables
EXECUTE sp_executesql @sql
October 5, 2009 at 12:26 am
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
October 5, 2009 at 3:15 am
thanks for the suggestion mays.
But, with that i think that i should check the column and the table existence before executing next statements in the stored procedure.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 5, 2009 at 3:51 am
declare @cols as nvarchar(50)
declare @tables as nvarchar(50)
set @cols = 'Name'
set @tables = 'Bank'
if object_id(@tables)>0
begin
if (COLUMNPROPERTY( OBJECT_ID(@tables),@cols,'PRECISION'))>0
begin
select 'PASS'-- your execution statement
end
else
begin
select 'Column Fail'
end
end
else
select 'Table Fail'
October 5, 2009 at 1:54 pm
Just out of curiosity, what are you trying to achieve with this approach? Why would you query a table you don't know exists?
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply