Dynamic sql

[747576]

Hi there,

I am trying to generate some dynamic sql to print sql statements that can be copied and used to insert data into a table.

Below is some sql that first creates a temp table, then a procedure to generate the insert statements and the usage.

--TEMP TABLE
CREATE TABLE TEMPTABLE([FIELDTABLE] [varchar] (20)
                        ,[FIELDNAME] [varchar] (20)
						,[LOOKUP] [varchar] (20)
                        )
GO
INSERT INTO TEMPTABLE(FIELDTABLE,FIELDNAME,LOOKUP) VALUES ('CODE','COL1','AA')
INSERT INTO TEMPTABLE(FIELDTABLE,FIELDNAME,LOOKUP) VALUES ('CODE','COL1','BB')
INSERT INTO TEMPTABLE(FIELDTABLE,FIELDNAME,LOOKUP) VALUES ('CODE','COL1','CC')
INSERT INTO TEMPTABLE(FIELDTABLE,FIELDNAME,LOOKUP) VALUES ('CODE','COL1','SD')
INSERT INTO TEMPTABLE(FIELDTABLE,FIELDNAME,LOOKUP) VALUES ('CODE','COL2','CD')
INSERT INTO TEMPTABLE(FIELDTABLE,FIELDNAME,LOOKUP) VALUES ('CODE','COL2','YT')
INSERT INTO TEMPTABLE(FIELDTABLE,FIELDNAME,LOOKUP) VALUES ('CODE','COL2','SW')
INSERT INTO TEMPTABLE(FIELDTABLE,FIELDNAME,LOOKUP) VALUES ('CODE','COL2','QA')
GO

--PROCEDURE

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[DYNAMICSCRIPT] 
@fieldtable VARCHAR (50), @fieldname VARCHAR (50), @lookup VARCHAR (50)

AS
DECLARE @col1 VARCHAR (50)
DECLARE @col2 VARCHAR (50)
DECLARE @col3 VARCHAR (50)

DECLARE @cmd VARCHAR(4000)

SET @col1 = @fieldtable
SET @col2 = @fieldname
SET @col3 = @lookup

SET @cmd = 'INSERT INTO LOOKUP_TABLE (FIELDTABLE,FIELDNAME,LOOKUP) VALUES('''+@col1+''','''+@col2+''','''+@col3+''')'

PRINT (@cmd)

--USAGE

EXEC DYNAMICSCRIPT 'CODE','COL1','AA'

This will print the following statement:

INSERT INTO LOOKUP_TABLE (FIELDTABLE,FIELDNAME,LOOKUP) VALUES('CODE','COL1','AA')

This works fine until I want to create multiple statements
For example:

INSERT INTO LOOKUP_TABLE (FIELDTABLE,FIELDNAME,LOOKUP) VALUES('CODE','COL1','AA')
INSERT INTO LOOKUP_TABLE (FIELDTABLE,FIELDNAME,LOOKUP) VALUES('CODE','COL2','YT')

I was thinking I could use a select statement and the procedure in a loop or cursor to generate each statement in turn:

SELECT * FROM TEMPTABLE WHERE FIELDTABLE = 'CODE' AND FIELDNAME IN ('COL1','COL2') AND LOOKUP IN('AA','YT')

FIELDTABLE FIELDNAME LOOKUP
CODE COL1 AA
CODE COL2 YT

so for each record returned by the select statement use the fields as variables to pass into the procedure.

Does anyone know the sql for the loop?

Thank you

 

[bborissov]

Why you need SP to do this?
You can easily insert records directly from the table:

INSERT INTO LOOKUP_TABLE (FIELDTABLE,FIELDNAME,LOOKUP)
SELECT FIELDTABLE,FIELDNAME,LOOKUP
      FROM TEMPTABLE
WHERE FIELDTABLE = 'CODE'          AND
      FIELDNAME IN ('COL1','COL2') AND
      LOOKUP IN('AA','YT')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.