Solved

Automatic Creation of a new SQL View using Parameters

Posted on 2008-10-28
17
549 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
ID: 22826098
   
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
ID: 22826153
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
ID: 22826166
oop. looks like I was a bit too late!
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:okacs
ID: 22826196
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
ID: 22826219
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
ID: 22826238
try:
 (D.Flag IS NULL) AND (A.GroupId =''' + cast(@NewGRoupID as nvarchar(20)) + ''')'
0
 
LVL 8

Expert Comment

by:tony_angelopoulos
ID: 22826251
ha ha, again I was beaten!

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

Author Comment

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

0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22826264
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
ID: 22826273
Still get Conversion failed when converting from a character string to uniqueidentifier.   =(  =(
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22826291
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
ID: 22826371
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
ID: 22826386
Ahhh...I gotcha.  yes, try to cast both.
0
 

Author Comment

by:okacs
ID: 22826419
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
ID: 22826457
SET @NewID = 'TESTING'
 should be SET @NewID = NewID()

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

Author Comment

by:okacs
ID: 22826461
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
ID: 31510939
Thanks!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
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 to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 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