asked on

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.

chapmandew

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

tony_angelopoulos

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

tony_angelopoulos

oop. looks like I was a bit too late!

okacs

ASKER

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.

chapmandew

tony_angelopoulos

try:
(D.Flag IS NULL) AND (A.GroupId =''' + cast(@NewGRoupID as nvarchar(20)) + ''')'

tony_angelopoulos

ha ha, again I was beaten!

Guess it's back to mavis beacon...

okacs

ASKER

Conversion failed when converting from a character string to uniqueidentifier. =(

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

okacs

ASKER

Still get Conversion failed when converting from a character string to uniqueidentifier. =( =(

chapmandew

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.

okacs

ASKER

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))+ ''')'

chapmandew

Ahhh...I gotcha. yes, try to cast both.

okacs

ASKER

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

tony_angelopoulos

SET @NewID = 'TESTING'
should be SET @NewID = NewID()

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

okacs

ASKER

LOL. I figured it out. It wasnt the WHERE clause, it was the SET. Hehe. It completes successfully now! Thanks!

okacs

ASKER

Thanks!