August 4, 2015 at 8:17 am
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the parametarized query.
Could you tell me if there is a solution to that issue?
Below please find my code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
Declare @TSN AS NVarchar(20)
Declare @VName AS Varchar(20)
SET @TSN = SUBSTRING(@TableName,6,3)
SET @TSN = @TSN + '_DIST_CD'
SET @VName = @TableName + '_' + @Dist
SET @SQLQuery = N'exec mpe.dbo.sp_executesql ' + 'N''CREATE VIEW ' + @VName + ' AS Select * From dbo.' + @TableName + ' WHERE ' + @TSN + ' =@Dist ' + ''''
EXEC sp_executesql @SQLQuery,
N'@VName Varchar(20) output, @TableName Varchar(20) output, @TSN NVarchar(20) output, @Dist Varchar(20) output',
@VName output, @TableName output, @TSN output, @Dist output
August 4, 2015 at 8:24 am
blossom_suki (8/4/2015)
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the parametarized query.
Could you tell me if there is a solution to that issue?
Below please find my code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
Declare @TSN AS NVarchar(20)
Declare @VName AS Varchar(20)
SET @TSN = SUBSTRING(@TableName,6,3)
SET @TSN = @TSN + '_DIST_CD'
SET @VName = @TableName + '_' + @Dist
SET @SQLQuery = N'exec mpe.dbo.sp_executesql ' + 'N''CREATE VIEW ' + @VName + ' AS Select * From dbo.' + @TableName + ' WHERE ' + @TSN + ' =@Dist ' + ''''
EXEC sp_executesql @SQLQuery,
N'@VName Varchar(20) output, @TableName Varchar(20) output, @TSN NVarchar(20) output, @Dist Varchar(20) output',
@VName output, @TableName output, @TSN output, @Dist output
Hi and welcome to the forums. I would argue that you don't really want to do this. There are a number of things wrong with this approach. First is the name of your procedure. You should NOT use the sp_ prefix. I would suggest not using a prefix at all but the sp_ prefix is reserved by MS and is used to indicate a system procedure.
The second issue I see here is this is wide open to sql injection.
The third issue is using select *. You should be explicit in naming your columns and not return data you don't need. Also, in views, the * is NOT dynamic. If the table structure changes your view will be out of synch and can even start returning data in the wrong columns.
The next issue here is that you don't have any kind of validation of the name. It just blindly tries to create a view with whatever name you pass it. It should check for an existing object.
The biggest issue here though is the concept that you need a stored procedure to generate a view. Why can't you just create the view when you need one. Automating creation of objects in sql server almost never ends up well.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 4, 2015 at 8:43 am
To emphasize what Sean said, doing a SELECT * in a view is a form of "death by SQL". The SELECT * in views and functions is positional rather than name based. If someone adds a column to the "middle" of the table, the columns will be offset from the names and there will be no warnings that you're returning the wrong information in the wrong columns. Yes, you could use WITH SCHEMABINDING to at least prevent such a silent act from occurring but that opens a whole 'nuther can of worms.
Also, you're creating code by concatenating parameter content without sanitizing it for SQL Injection.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2015 at 8:44 am
I have to agree with Sean on all his points. Looking at your code, however, I see other problems as well.
You're not using sp_executesql correctly. Your invocation of the procedure has errors.
There is no reason to embed another call to sp_executesql inside the dynamic SQL you invoke using sp_executesql.
You don't validate if the table name you pass even exists before creating the view. SQL will still allow this under the assumption that you will create the table. I believe that is called deferred resolution.
What is the business case for using a stored procedure to create a view?
August 4, 2015 at 8:46 am
Actually, I need to create 20 views for each database, in the system we have more than 90 databases.
Thus, I was wondering if there is an automated way of creating views, by a procedure or function which will take as parameter the table name and a field value, and then create the view based on that input parameters.
August 4, 2015 at 8:54 am
blossom_suki (8/4/2015)
Actually, I need to create 20 views for each database, in the system we have more than 90 databases.Thus, I was wondering if there is an automated way of creating views, by a procedure or function which will take as parameter the table name and a field value, and then create the view based on that input parameters.
Are these 20 views the same in each of the 90 databases? If so, then you only have to write 20 views and execute your script on each database. That is a LOT simpler than a proc to create views. As it sits you would have to add that proc to each database already anyway. Just make sure you get rid of that select *.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 4, 2015 at 9:21 am
blossom_suki (8/4/2015)
Actually, I need to create 20 views for each database, in the system we have more than 90 databases.Thus, I was wondering if there is an automated way of creating views, by a procedure or function which will take as parameter the table name and a field value, and then create the view based on that input parameters.
Please explain what you are trying to accomplish. Show us by demonstrating with a sample table showing what the view over that table should look like when the script/procedure has run.
August 4, 2015 at 9:40 am
Sean Lange (8/4/2015)
blossom_suki (8/4/2015)
Actually, I need to create 20 views for each database, in the system we have more than 90 databases.Thus, I was wondering if there is an automated way of creating views, by a procedure or function which will take as parameter the table name and a field value, and then create the view based on that input parameters.
Are these 20 views the same in each of the 90 databases? If so, then you only have to write 20 views and execute your script on each database. That is a LOT simpler than a proc to create views. As it sits you would have to add that proc to each database already anyway. Just make sure you get rid of that select *.
To add to that and once the scripts for the views have been created, it may even be possible to use the undocumented sp_MSforeachdb command to do such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2015 at 9:43 am
SET Ansi_Nulls On;
SET Quoted_Identifier On;
GO
ALTER Procedure [dbo].[sp_ViewCreate]
@TableName varchar(100),
@Dist varchar(20)
AS
Set Nocount On;
Declare @SQLQuery AS nvarchar(4000)
Declare @ParamDefinition AS nvarchar(2000)
Declare @TSN AS nvarchar(20)
Declare @VName AS varchar(20)
SET @TSN = SUBSTRING(@TableName,6,3)
SET @TSN = @TSN + '_DIST_CD'
SET @VName = @TableName + '_' + @Dist
SET @SQLQuery = N'Exec mpe.dbo.sp_executesql ' +
'N''CREATE VIEW ' + @VName + ' AS ' +
'Select * From dbo.' + @TableName + ' ' +
'WHERE ' + @TSN + ' = ''''' + @Dist + ''''';'''
--PRINT @SQLQuery
EXEC sp_executesql @SQLQuery;
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 4, 2015 at 10:19 am
ScottPletcher (8/4/2015)
SET Ansi_Nulls On;
SET Quoted_Identifier On;
GO
ALTER Procedure [dbo].[sp_ViewCreate]
@TableName varchar(100),
@Dist varchar(20)
AS
Set Nocount On;
Declare @SQLQuery AS nvarchar(4000)
Declare @ParamDefinition AS nvarchar(2000)
Declare @TSN AS nvarchar(20)
Declare @VName AS varchar(20)
SET @TSN = SUBSTRING(@TableName,6,3)
SET @TSN = @TSN + '_DIST_CD'
SET @VName = @TableName + '_' + @Dist
SET @SQLQuery = N'Exec mpe.dbo.sp_executesql ' +
'N''CREATE VIEW ' + @VName + ' AS ' +
'Select * From dbo.' + @TableName + ' ' +
'WHERE ' + @TSN + ' = ''''' + @Dist + ''''';'''
--PRINT @SQLQuery
EXEC sp_executesql @SQLQuery;
GO
Could not post my code directly here from work so please see attached file.
August 4, 2015 at 1:10 pm
Here is Lynn's code in case anybody wants to see it.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[sp_ViewCreate]
@TableName varchar(100),
@Dist varchar(20)
AS
set nocount on;
declare @SQLCmd nvarchar(max),
@SQLParm nvarchar(max),
@ColumnList nvarchar(max),
@TSN nvarchar(20),
@VName varchar(121); -- modifed length of variable to account for 100 character table name and 20 character dist value
set @TSN = substring(@TableName,6,3) + '_DIST_CD'; -- no reason for two set commands
set @VName = @TableName + '_' + @Dist;
if object_id('dbo.' + @TableName) is not null
begin
set @ColumnList = stuff((select N', ' + col.name
from sys.columns col
where col.object_id = object_id('dbo.' + @TableName)
order by col.column_id
for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'');
set @SQLCmd = N'
if object_id(''dbo.' + @VName + N''') is null
CREATE VIEW dbo.' + @VName + N' AS
SELECT ' + @ColumnList + N'
FROM dbo.' + @TableName + N'
WHERE ' + @TSN + N' = @DistVal;';
print @SQLCmd;
set @SQLParm = N'@DistVal varchar(20)';
exec sys.sp_executesql @SQLCmd, @SQLParm, @DistVal = @Dist;
end
set nocount off;
GO
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 4, 2015 at 1:17 pm
Thanks you all for the feedback.
I would like to accomplish an automatic way of creating views with the same WHERE condition in more than 90 different tables.
The database stores information about automotive parts and all the tables have different structure.
However, in all tables there is a column which corresponds to the distributor associated with the automotive part (each row of tables).
So, in all tables I would like to make a view for each distinct value of this column (Distr_Code, so each distributor can access only their products).
Then, after creating database accounts, I will define appropriate permissions to views so they can be accessed from specific users.
For example we have the following table (PFCMST).
Part_Number Part_Descr. COL3 COL4 ... Distr_Code
AF423JJQQQQQ ... ... ... A112
AF4678JAAAAA ... ... ... A113
AT3448IUUUUU ... ... ... A113
...... ... .... ...
AE4557RRRRR ... ... ... A139
Then I need to create views for each Distr_Code such as
CREATE VIEW PFCMST_A112 AS SELECT * FROM PFCMST WHERE Distr_Code = 'A112'
CREATE VIEW PFCMST_A113 AS SELECT * FROM PFCMST WHERE Distr_Code = 'A113'
...... ... .... ...
CREATE VIEW PFCMST_A139 AS SELECT * FROM PFCMST WHERE Distr_Code = 'A139'
Edit: I forgot to mention that the structure of tables will not be changed but the data will be updated almost in a daily basis.
August 4, 2015 at 1:24 pm
I believe that's exactly what the code I posted will do. You can uncomment the PRINT statement to verify the views that are being created.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 4, 2015 at 2:05 pm
blossom_suki (8/4/2015)
Thanks you all for the feedback.I would like to accomplish an automatic way of creating views with the same WHERE condition in more than 90 different tables.
The database stores information about automotive parts and all the tables have different structure.
However, in all tables there is a column which corresponds to the distributor associated with the automotive part (each row of tables).
So, in all tables I would like to make a view for each distinct value of this column (Distr_Code, so each distributor can access only their products).
Then, after creating database accounts, I will define appropriate permissions to views so they can be accessed from specific users.
For example we have the following table (PFCMST).
Part_Number Part_Descr. COL3 COL4 ... Distr_Code
AF423JJQQQQQ ... ... ... A112
AF4678JAAAAA ... ... ... A113
AT3448IUUUUU ... ... ... A113
...... ... .... ...
AE4557RRRRR ... ... ... A139
Then I need to create views for each Distr_Code such as
CREATE VIEW PFCMST_A112 AS SELECT * FROM PFCMST WHERE Distr_Code = 'A112'
CREATE VIEW PFCMST_A113 AS SELECT * FROM PFCMST WHERE Distr_Code = 'A113'
...... ... .... ...
CREATE VIEW PFCMST_A139 AS SELECT * FROM PFCMST WHERE Distr_Code = 'A139'
Edit: I forgot to mention that the structure of tables will not be changed but the data will be updated almost in a daily basis.
You only need an itvf that takes Dist ('A113' for example) as a parameter to return the data you need. I'd post it for you but it I can't from here unless I write it, save it as a file, then upload it.
August 4, 2015 at 2:21 pm
Since Lynn is unable to post code I took the liberty of tossing together an example of how you could do as an itvf. This is certainly a lot easier than generating 20+ views.
create function SomeFunctionName
(
@Distr_Code varchar(4) --or whatever the appropriate datatype is
) RETURNS TABLE AS
RETURN
SELECT * --or whatever columns are appropriate
FROM PFCMST
WHERE Distr_Code = @Distr_Code
Then it is really simple to use.
select *
from SomeFunctionName('A113')
Or maybe you need to pull the Distr_Code from another table.
select *
from SomeOtherTable sot
cross apply dbo.SomeFunctionName(sot.Distr_Code) sfn
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply