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/