Passing tableName as parameter to SP

13 Sep 2006 12:51 PM

Ann

Hi all,

i am trying to retrieve the records based on certain column,where the
tablename as well as columname is passed as paramater.Below is the SP.It
works when i omit the where condiiton.Pls tell me where i am going wrong.

ALTER PROCEDURE [dbo].[GetDataOnID]
    @TableName varchar(50),
    @TableId int
AS
declare @SelectCmd nvarchar(200)
BEGIN
    Set @SelectCmd='Select * from '+@TableName + 'where id
='+convert(varchar,@TableId)
    EXEC(@SelectCmd)

END

Thanks

13 Sep 2006 1:01 PM

Aaron Bertrand [SQL Server MVP]

When you have the where condition, what does "doesn't work" mean? Do you
get an error message? No data? The wrong data? Too much data?

For starters, NEVER use CONVERT(VARCHAR without specifying a size.

Set @SelectCmd='Select * from '+@TableName + 'where id = ' +
CONVERT(VARCHAR(12),@TableId);

If you don't specify a size, you risk default truncation to VARCHAR(1). So
if you are looking for id = 567 you will actually get id = 5.

In any case, if you can elaborate on how the behavior differs when "it
works" and when it doesn't, we may be able to provide more useful help.

You should also read this article. (Why you need a single stored procedure
to SELECT * from <user-defined table> I don't quite understand.)

http://www.sommarskog.se/dynamic_sql.html

Show quote

"Ann" <A**@discussions.microsoft.com> wrote in message
news:92E226E3-DDB1-47C1-AFCD-52257A3E36CF@microsoft.com...
> Hi all,
>
> i am trying to retrieve the records based on certain column,where the
> tablename as well as columname is passed as paramater.Below is the SP.It
> works when i omit the where condiiton.Pls tell me where i am going wrong.
>
> ALTER PROCEDURE [dbo].[GetDataOnID]
> @TableName varchar(50),
> @TableId int
> AS
> declare @SelectCmd nvarchar(200)
> BEGIN
> Set @SelectCmd='Select * from '+@TableName + 'where id
> ='+convert(varchar,@TableId)
> EXEC(@SelectCmd)
>
> END
>
>
> Thanks

13 Sep 2006 1:12 PM

Aaron Bertrand [SQL Server MVP]

> Set @SelectCmd='Select * from '+@TableName + 'where id = ' +
> CONVERT(VARCHAR(12),@TableId);

You also need a space between the tablename and the WHERE keyword.

SET @SelectCmd = 'SELECT * FROM '+@TableName
+ ' WHERE id = ' + CONVERT(VARCHAR(12),@TableId);

Debugging 101:

Print @sql instead of EXEC(@sql) would have made this problem more obvious.
(As would telling us what "broken" means.)

13 Sep 2006 1:27 PM

Ann

Thanks...
i could nt see this small mistake of putting space..it works now...

Thanks a lot....

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> > Set @SelectCmd='Select * from '+@TableName + 'where id = ' +
> > CONVERT(VARCHAR(12),@TableId);
>
> You also need a space between the tablename and the WHERE keyword.
>
> SET @SelectCmd = 'SELECT * FROM '+@TableName
> + ' WHERE id = ' + CONVERT(VARCHAR(12),@TableId);
>
>
> Debugging 101:
>
> Print @sql instead of EXEC(@sql) would have made this problem more obvious.
> (As would telling us what "broken" means.)
>
>
>

13 Sep 2006 1:21 PM

Ann

Thanks for your detail response...
i know this is not a good practice,but this is not the actual requirement..i
jst started....

i am getting an error message,telling...

Incorrect syntax near '='.

can u tell me what i am doing wrong...

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> When you have the where condition, what does "doesn't work" mean? Do you
> get an error message? No data? The wrong data? Too much data?
>
> For starters, NEVER use CONVERT(VARCHAR without specifying a size.
>
> Set @SelectCmd='Select * from '+@TableName + 'where id = ' +
> CONVERT(VARCHAR(12),@TableId);
>
> If you don't specify a size, you risk default truncation to VARCHAR(1). So
> if you are looking for id = 567 you will actually get id = 5.
>
> In any case, if you can elaborate on how the behavior differs when "it
> works" and when it doesn't, we may be able to provide more useful help.
>
> You should also read this article. (Why you need a single stored procedure
> to SELECT * from <user-defined table> I don't quite understand.)
>
> http://www.sommarskog.se/dynamic_sql.html
>
>
>
>
> "Ann" <A**@discussions.microsoft.com> wrote in message
> news:92E226E3-DDB1-47C1-AFCD-52257A3E36CF@microsoft.com...
> > Hi all,
> >
> > i am trying to retrieve the records based on certain column,where the
> > tablename as well as columname is passed as paramater.Below is the SP.It
> > works when i omit the where condiiton.Pls tell me where i am going wrong.
> >
> > ALTER PROCEDURE [dbo].[GetDataOnID]
> > @TableName varchar(50),
> > @TableId int
> > AS
> > declare @SelectCmd nvarchar(200)
> > BEGIN
> > Set @SelectCmd='Select * from '+@TableName + 'where id
> > ='+convert(varchar,@TableId)
> > EXEC(@SelectCmd)
> >
> > END
> >
> >
> > Thanks
>
>
>