Solved

Automatic Creation of a new SQL View using Parameters

Posted on 2008-10-28
17
574 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

617 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