Solved

Automatic Creation of a new SQL View using Parameters

Posted on 2008-10-28
17
526 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:okacs
  • 7
  • 5
  • 5
17 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
   
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
 
LVL 8

Expert Comment

by:tony_angelopoulos
Comment Utility
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
 
LVL 8

Expert Comment

by:tony_angelopoulos
Comment Utility
oop. looks like I was a bit too late!
0
 

Author Comment

by:okacs
Comment Utility
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
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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
 
LVL 8

Expert Comment

by:tony_angelopoulos
Comment Utility
try:
 (D.Flag IS NULL) AND (A.GroupId =''' + cast(@NewGRoupID as nvarchar(20)) + ''')'
0
 
LVL 8

Expert Comment

by:tony_angelopoulos
Comment Utility
ha ha, again I was beaten!

Guess it's back to mavis beacon...
0
 

Author Comment

by:okacs
Comment Utility
Conversion failed when converting from a character string to uniqueidentifier.   =(

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
Comment Utility
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
 

Author Comment

by:okacs
Comment Utility
Still get Conversion failed when converting from a character string to uniqueidentifier.   =(  =(
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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
 

Author Comment

by:okacs
Comment Utility
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
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
Ahhh...I gotcha.  yes, try to cast both.
0
 

Author Comment

by:okacs
Comment Utility
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
 
LVL 8

Expert Comment

by:tony_angelopoulos
Comment Utility
SET @NewID = 'TESTING'
 should be SET @NewID = NewID()

(just in case I'm just trying to help)
0
 

Author Comment

by:okacs
Comment Utility
LOL.  I figured it out.  It wasnt the WHERE clause, it was the SET.  Hehe.  It completes successfully now!  Thanks!
0
 

Author Closing Comment

by:okacs
Comment Utility
Thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now