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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy