stored procedure problem

I am trying to write a stored procedure that I can execute with a given client name, and pass that client name as param to the create table statement inside the stored procedure.  Here is the syntax I have:

IF EXISTS (SELECT name
         FROM   sysobjects
         WHERE  name = 'Build_Invoice'
         AND         type = 'P')
    DROP PROCEDURE Build_Invoice
GO

CREATE PROCEDURE Build_Invoice (@Client Varchar(30) = " ")
AS
Set @Client=@Client + '_Invoice'

CREATE TABLE dbo.@Client (
      [Column1] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
                [Column2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO


I am testing in by running this:

execute Build_Invoice 'Test' and expecting to get Test_Invoice as table name.  I can create the sp just fine, but I cannot pass the param.  Help!!!

Thanks in advance,

IPT
iptraderAsked:
Who is Participating?
Advertise Here
 
SjoerdVerweijConnect With a Mentor Commented:
Alrighty. Did my code work though?
0
 
SjoerdVerweijCommented:
CREATE PROCEDURE Build_Invoice (@Client Varchar(30) = " ")
AS
begin

  set nocount on

  declare @sql varchar(8000)

  set @sql = 'CREATE TABLE dbo.[' + @client + '_invoice'] (' +
      '[Column1] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , ' +
      '[Column2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ' +
      ') ON [PRIMARY] '

  execute (@sql)

  set nocount off
end
go

But why not create one invoice table, with ClientName as a field? It would simplify SO many things...
0
 
iptraderAuthor Commented:
The table is actually part of an overall bigger beast involving an ETL process.  Metadata for this table HAS to be exact.

:)
0
 
LowfatspreadCommented:
Then would it not be better to have a Standard set of tables....

and use the schema name to separate the clients ...

ie create     CommonDB.Client1.invoice  

also you sp should be more like...
CREATE PROCEDURE Build_Invoice (@Client Varchar(30) = " ")
AS
Declare @TableName Varchar(40)
declare @sql varchar(8000)

Set @TableNAme=@Client + '_Invoice'

set @SQL = 'CREATE TABLE dbo.' + @TableName
 +' ( [Column1] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,'
+'   [Column2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL '
+ ') ON [PRIMARY]'

exec (@sql)
set @SQL = 'GRANT Select on dbo.' + @tablename + ' to ' + @client
exec (@SQL)

GO
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.

All Courses

From novice to tech pro — start learning today.