Solved

Automatic Creation of a new SQL View using Parameters

Posted on 2008-10-28
17
564 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Job Hung 17 35
Can I skip a node in XML? 9 30
Related to SQL Query 5 18
SQL - Error "The Multi-Part Identifier could not be found" 2 12
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

713 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