Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Using Dynamic SQL results in a stored procedure

Author  Topic  Jay99 468 Posts Posted - 2001-03-05 : 12:43:28 SQL Server 7, latest ServicePack, on NT 4.0, latest Service Pack. I am writing an application to write Stored Procedures. Developers chooses from GUI the parameters that he/she wants and which tables they come from. Then I exec a stored procedure that gives me the shell of my stored procedure and I can save it off to a file, fill in the blanks, and compile. One thing I want to do is check to make sure the params on the sp that I am generating aren't missing if they are not nullable. I have written a stored procedure to look and see if the field they want to use as a parameter is nullable. . . CREATE PROCEDURE usp_utilAmINull @vcServer VARCHAR(30), @vcDatabase VARCHAR(30), @vcTable VARCHAR(50), @vcField VARCHAR(30) AS SET NOCOUNT ON DECLARE @vcSQL VARCHAR(500) SELECT @vcSQL = 'SELECT ISNullable FROM ' + @vcServer + N'.' + @vcDatabase + N'.dbo.syscolumns sc INNER JOIN ' + @vcServer + N'.' + @vcDatabase + N'.dbo.sysobjects so on SC.id = so.id WHERE so.xtype = ''U'' AND so.name = ''' + @vcTable + ''' AND sc.name = ''' + @vcField + '''' IF (@@ERROR <> 0) GOTO Error_Condition EXEC (@vcSQL) RETURN(0) --On error . . . Error_Condition: RETURN(1) GO Now in my generator Stored Procedure I want to do this . . . IF (EXEC (usp_utilAmINull @vcServer, @vcDatabase, @vcTable, @vcName)) = 0 BEGIN SELECT @vcIsNullList = @vcIsNullList + 'IF @' + @vcName + 'IS NULL BEGIN RAISERROR(''Null ' + @vcName + ' passed to ' + @vcProcName + ''',16,1) WITH LOG GOTO Error_Condition END ' END Obviously the red stuff generates "Server: Msg 156, Level 15, State 1, Procedure usp_genSPScriptASP, Line 76 Incorrect syntax near the keyword 'EXEC'." error. If this were in SQL2000, I could write a function to look at the null-a-bility . But in 7, it looks like I can only do it with Dynamic SQL, but I can't figure out how to use the resultset from usp_utilAmINull. Any suggestions? Jay