Automatic Creation of a new SQL View using Parameters

I am using MS SQL Server 2005 and the Server Managemenent Studio.  I have a stored procedure that I use to preform several row copy functions between tables.  I would like to add a new function to it that would create a new SQL VIEW each time is it run.    The NAME of the view created and part of the WHERE clause would need to be built based on the value of pre-existing parameters used previously in the stored procedure.

(The resulting VIEW that is created would NOT contain parameters)

I'm trying to use this code:

     -- ===============================================================
     -- Now create the VIEW that Group will pull from
     -- ===============================================================
     CREATE VIEW [dbo].[@NewGroupName] AS
     SELECT
          A.AccountNumber, C.FullName, C.PhoneNumber
     FROM
          dbo.ActiveData AS A
          INNER JOIN dbo.Customers AS C ON c.CustomerId = cd.CustomerId
     WHERE
          (D.Flag IS NULL) AND (A.GroupId = @NewGRoupID)

To create a view like this:

     CREATE VIEW [dbo].[SouthWestGroup11] AS
     SELECT
          A.AccountNumber, C.FullName, C.PhoneNumber
     FROM
          dbo.ActiveData AS A
          INNER JOIN dbo.Customers AS C ON c.CustomerId = cd.CustomerId
     WHERE
          (D.Flag IS NULL) AND (A.GroupId = 001245873)

I'm getting the error "line 170 - incorrect syntax near the keyword VIEW".  I figgure it is a problem with instantiation of the parameters, but I dont know how to fix it.

Thanks.
okacsAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
this fails?

declare @sql nvarchar(4000)


set @sql = 'CREATE VIEW [dbo].[' + @NewGroupName + '] AS
     SELECT
          A.AccountNumber, C.FullName, C.PhoneNumber
     FROM
          dbo.ActiveData AS A
          INNER JOIN dbo.Customers AS C ON c.CustomerId = cd.CustomerId
     WHERE
          (D.Flag IS NULL) AND (A.GroupId = ''' + cast(@NewGRoupID as nvarchar(50))+ ''')'

exec sp_executesql @sql
0
 
chapmandewCommented:
   
declare @sql nvarchar(4000)


set @sql = 'CREATE VIEW [dbo].[' + @NewGroupName + '] AS
     SELECT
          A.AccountNumber, C.FullName, C.PhoneNumber
     FROM
          dbo.ActiveData AS A
          INNER JOIN dbo.Customers AS C ON c.CustomerId = cd.CustomerId
     WHERE
          (D.Flag IS NULL) AND (A.GroupId = ''' + @NewGRoupID+ ''')'

exec sp_executesql @sql
0
 
tony_angelopoulosCommented:
You need to use dynamic SQL.  Inside your sproc:
declare @sql varchar(8000)
set @sql = 'CREATE VIEW [dbo].[' + @NewGroupName+ '] AS
     SELECT
          A.AccountNumber, C.FullName, C.PhoneNumber
     FROM
          dbo.ActiveData AS A 
          INNER JOIN dbo.Customers AS C ON c.CustomerId = cd.CustomerId 
     WHERE
          (D.Flag IS NULL) AND (A.GroupId =''' +  @NewGRoupID + ''')'
 
exec(@sql)

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
tony_angelopoulosCommented:
oop. looks like I was a bit too late!
0
 
okacsAuthor Commented:
Almost there....  Still get one error:  "The data types varchar and uniqueidentifier are incompatible in the add operator."

The parameter @NewGRoupID is actually a uniqueidentifier.  I posted it in the question as an int for simplicity, but apparently it matters...  I tried both + and & and neither works.

Thanks.
0
 
chapmandewCommented:
declare @sql nvarchar(4000)


set @sql = 'CREATE VIEW [dbo].[' + @NewGroupName + '] AS
     SELECT
          A.AccountNumber, C.FullName, C.PhoneNumber
     FROM
          dbo.ActiveData AS A
          INNER JOIN dbo.Customers AS C ON c.CustomerId = cd.CustomerId
     WHERE
          (D.Flag IS NULL) AND (A.GroupId = ''' + cast(@NewGRoupID as varchar(50))+ ''')'

exec sp_executesql @sql
0
 
tony_angelopoulosCommented:
try:
 (D.Flag IS NULL) AND (A.GroupId =''' + cast(@NewGRoupID as nvarchar(20)) + ''')'
0
 
tony_angelopoulosCommented:
ha ha, again I was beaten!

Guess it's back to mavis beacon...
0
 
okacsAuthor Commented:
Conversion failed when converting from a character string to uniqueidentifier.   =(

0
 
okacsAuthor Commented:
Still get Conversion failed when converting from a character string to uniqueidentifier.   =(  =(
0
 
chapmandewCommented:
can't be...you're not converting to a guid, you're converting from a guid...are you doing an exact copy and paste?  Post the rest of your code.
0
 
okacsAuthor Commented:
both sides of the equation are GUID.  A.GroupId  is a uniqueidentifier in the table, and @NewGRoupID  is declared as a uniqueidentifier.

     AND (A.GroupId = ''' + cast(@NewGRoupID as nvarchar(50))+ ''')'

So do I need to cast them both first liek this?

     AND (cast(A.GroupId as nvarchar(50))  = ''' + cast(@NewGRoupID as nvarchar(50))+ ''')'
0
 
chapmandewCommented:
Ahhh...I gotcha.  yes, try to cast both.
0
 
okacsAuthor Commented:
nope, that does not work either...
Here is the code:
declare @Sqltxt nvarchar(4000)
 
-- ===============================
-- These declarations here temporarily for testing only.  They are declared and set above. 
-- ===============================
declare @Customdata varchar(1024)
set @Customdata = 'TESTING - Delete this View'
DECLARE @NewID	uniqueidentifier
SET @NewID = 'TESTING'
 
set @Sqltxt = 'CREATE VIEW [dbo].[' + @Customdata +'] AS
SELECT	
	AD.WorkOrderId, AD.AccountNumber, c.FullName, c.PhoneNumber
FROM	
	dbo.ActiveData AS Ad 
	INNER JOIN dbo.Customers AS C ON c.CustomerId = AD.CustomerId 
WHERE     (AD.Flag IS NULL) AND (cast(AD.ID as nvarchar(50)) = ''' + cast(@NewID as nvarchar(50))+ ''')'
 
exec sp_executesql @Sqltxt

Open in new window

0
 
tony_angelopoulosCommented:
SET @NewID = 'TESTING'
 should be SET @NewID = NewID()

(just in case I'm just trying to help)
0
 
okacsAuthor Commented:
LOL.  I figured it out.  It wasnt the WHERE clause, it was the SET.  Hehe.  It completes successfully now!  Thanks!
0
 
okacsAuthor Commented:
Thanks!
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.