Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.
Solved
stored procedure problem
Posted on 2004-07-30
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_
[Column2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_
) 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
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_
[Column2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_
) 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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
4 Comments
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_
'[Column2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_
') 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...
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_
'[Column2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_
') 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...
The table is actually part of an overall bigger beast involving an ETL process. Metadata for this table HAS to be exact.
:)
:)
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_
+' [Column2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_
+ ') ON [PRIMARY]'
exec (@sql)
set @SQL = 'GRANT Select on dbo.' + @tablename + ' to ' + @client
exec (@SQL)
GO
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_
+' [Column2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_
+ ') ON [PRIMARY]'
exec (@sql)
set @SQL = 'GRANT Select on dbo.' + @tablename + ' to ' + @client
exec (@SQL)
GO
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Course of the Month11 days, 7 hours left to enroll
719 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.