SQL SERVER 2008 Dynamic query problem
- by priyanka.sarkar
I have a dynamic query which reads like this
Alter PROCEDURE dbo.mySP
-- Add the parameters for the stored procedure here
(
@DBName varchar(50),
@tblName varchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @string as varchar(50)
declare @string1 as varchar(50)
set @string1 = '[' + @DBName + ']' + '.[dbo].' + '[' + @tblName + ']'
set @string = 'select * from ' + @string1
exec @string
END
I am calling like this
dbo.mySP 'dbtest1','tblTest'
And I am experiencing an error
"Msg 203, Level 16, State 2, Procedure mySP, Line 27
The name 'select * from [dbtest1].[dbo].[tblTest]' is not a valid identifier."
What is wrong? and How to overcome?
Thanks in advance
