Problem executing stored procedure... 2

[NewFromMattel]

I can compile the following sproc, but when I execute it, it fails on eval(). I thought this was a valid function.

Thanks

/* This sproc fixed the master form build table to include a specified missing
base table row for a specified form.
To call this sproc:
EXEC FixMFBForForm 'formname', 'basetable', 'applicationDSN'
*/
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'FixMFBForForm'
AND type = 'P')
DROP PROCEDURE FixMFBForForm
GO

CREATE PROCEDURE FixMFBForForm
@formName varchar(50), @baseTable varchar(25), @appDSN varchar(25)
AS

DECLARE @formTableName varchar(50)
DECLARE @masterFieldList varchar(50)
DECLARE @masterFormBuild varchar(50)
DECLARE @list varchar(4000)

SET @formTableName = 'UCFORMS.dbo.' + @formName + '_FieldList'
SET @masterFieldList = @appDSN + '.dbo.Master_Field_List'
SET @masterFormBuild = @appDSN + '.dbo.Master_Form_Build'

/* Here I get the list of necessary fields from the formname_fieldlist table */
SELECT @list = CASE WHEN @list IS NULL THEN FieldName ELSE @list + ',' + FieldName END
FROM eval(@formTableName)
WHERE FieldName IN -- Where the fieldname is in the master_field_list table for the given basetable
(SELECT Field_Name FROM eval(@masterFieldList) WHERE Base_Table_Name = @baseTable)

/* Now that I have my list of fields, let's see if we need it. */
IF NOT Exists
(SELECT Form_Name FROM eval(@masterFormBuild) WHERE Form_Name = @formName AND Table_Name = @baseTable)
BEGIN
INSERT INTO eval(@masterFormBuild)
(Form_Name, Table_Name, Field_List)
VALUES
(@formName, @baseTable, @list)
END
GO

 

[checkai]

What does the error message say? and what is EVAL supposed to do?

dlc

 

[NewFromMattel]

Basically, I'll be executing this sproc from within some other function... perhaps another sproc.

And I'll be passing in certain parameters, or creating local parameters within the sproc. Some of these are dynamically build table names. Like this snippet:

FROM eval(@formTableName)

I'm passing in information to build the correct table name int the format: databasename.dbo.tablename, but to get the sproc to compile I had to put wrap the variable (e.g., @formTableName) within an eval().

The sproc I pasted in above compiled, but when I tried to execute it in query analyzer, I got the following error message:
Server: Msg 208, Level 16, State 1, Procedure FixMFBForForm, Line 16
Invalid object name 'eval'.
Server: Msg 208, Level 16, State 1, Procedure FixMFBForForm, Line 16
Invalid object name 'eval'.

Thanks

 

[jby1]

eval is not a SQL Server function name

 

[NewFromMattel]

Is there a sql server function that can "evaluate" that variable to be used as a tablename?

 

[jby1]

Not that I know of.

The only way I know of to use a dynamically passed in table name is to build up a query string and run it using EXEC

 

[zhavic]

As 'jby1' sad, you need to use dynamic sql and EXECUTE statement or EXECUTE sp_executesql.

Example:

IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'FixMFBForForm'
       AND       type = 'P')
    DROP PROCEDURE FixMFBForForm
GO

CREATE PROCEDURE FixMFBForForm
    @formName varchar(50), @baseTable varchar(25), @appDSN varchar(25)
AS

DECLARE    @formTableName varchar(50)
DECLARE    @masterFieldList varchar(50)
DECLARE    @masterFormBuild varchar(50)
DECLARE    @list varchar(4000)

DECLARE @cStatement nvarchar(1000)
DECLARE @bExists bit

SET    @formTableName = 'UCFORMS.dbo.' + @formName + '_FieldList'
SET    @masterFieldList = @appDSN + '.dbo.Master_Field_List'
SET    @masterFormBuild = @appDSN + '.dbo.Master_Form_Build'


/* Here I get the list of necessary fields from the formname_fieldlist table */
SET @cStatement = 'SELECT @list = CASE WHEN @list IS NULL THEN FieldName ELSE @list + '','' + FieldName END ' +
					'FROM ' + @formTableName + ' ' +
					'WHERE FieldName IN ' +  -- Where the fieldname is in the master_field_list table for the given basetable
					    '(SELECT  Field_Name FROM ' + @masterFieldList + ' WHERE Base_Table_Name = @baseTable )'

EXECUTE sp_executesql @cStatement,
					  N'@list varchar(4000) OUTPUT, @baseTable varchar(25)',
					  @list OUTPUT,
					  @baseTable

/* Now that I have my list of fields, let's see if we need it. */
SET @cStatement = 'IF NOT Exists( SELECT Form_Name FROM ' + @masterFormBuild + ' WHERE Form_Name = @formName AND Table_Name = @baseTable ) ' + 
						'BEGIN ' +
						    'INSERT  INTO ' + @masterFormBuild + ' ' +
						    '(Form_Name, Table_Name, Field_List) ' + 
						    'VALUES ' +
						    '( @formName, @baseTable, @list) ' +
						'END'

EXECUTE sp_executesql @cStatement,
					  N'@formName varchar(50), @baseTable varchar(25), @list varchar(4000)',
					  @formName,
					  @baseTable,
					  @list

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.