February 7, 2005 at 12:12 am
Hello All,
This is my procedure... My Problem here is ... i want to execute the Satement in Red inside the procedure... This is adymamic SQL statement..
Create PROC Sp_EnterTextFile @filename sysname
as
BEGIN
SET nocount ON
declare @Tablecol varchar(2550), @Tablename varchar(200),@stmt varchar(8000), @column varchar(2400), @statement varchar(400),@statement1 varchar(2400), @cind as int , @fieldcount as int, @tmpfieldcount as int
CREATE TABLE #tempf (line varchar(8000))
EXEC ('bulk INSERT #tempf FROM "' + @filename + '"')
DECLARE pcursor CURSOR FOR
SELECT * FROM #tempf
OPEN pcursor
FETCH NEXT FROM pcursor into @Tablecol
WHILE @@FETCH_STATUS = 0
BEGIN
select @cind=CHARINDEX('|',rtrim(ltrim(@Tablecol))) --- finding 1st column Before Pipe
select @Tablename= dbo.udf_get_string(@Tablecol,1,'|') --- Getting Table name
select @column=SUBSTRING(rtrim(ltrim(@Tablecol)),@cind+1,len(@Tablecol)) --- getting column names
--select count(*) from dbo.Split (@column,'|') as int ---- Taking Column Count
SELECT 'INSERT INTO ' + @Tablename + '(' AS stmt
UNION ALL
SELECT
CHAR(9) + COL_NAME(OBJECT_ID(@Tablename), ORDINAL_POSITION) +
CASE WHEN ORDINAL_POSITION < (select count(*) from dbo.Split (@column,'|') as int) THEN ',' ELSE '' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Tablename AND ORDINAL_POSITION <= (select count(*) from dbo.Split (@column,'|') as int)
UNION ALL
SELECT ') Values ('
UNION ALL
SELECT
CASE c.DATA_TYPE
WHEN ('char') THEN ''''+dbo.udf_get_string(@Tablecol,number+1,'|')+''''
WHEN ('varchar') THEN ''''+dbo.udf_get_string(@Tablecol,number+1,'|')+''''
WHEN ('datetime') THEN REPLACE(rtrim(ltrim(dbo.udf_get_string(@Tablecol,number+1,'|'))) , '.' , '/' )
----dbo.udf_get_string(@Tablecol,number+1,'|')
ELSE
dbo.udf_get_string(@Tablecol,number+1,'|')
end
+ CASE WHEN n.number < (select count(*) from dbo.Split (@column,'|') as int) THEN ',' ELSE '' END
FROM master.dbo.spt_values n
INNER JOIN INFORMATION_SCHEMA.COLUMNS c
ON c.TABLE_NAME = @Tablename AND c.ORDINAL_POSITION = n.number
WHERE n.type = 'P'
AND n.number > 0
AND n.number <= (select count(*) from dbo.Split (@column,'|') as int)
UNION ALL
SELECT ')'
FETCH NEXT FROM pcursor into @Tablecol
END
CLOSE pcursor
DEALLOCATE pcursor
DROP TABLE #tempf
END
Thanks For help...
February 7, 2005 at 1:56 am
Couldn't you use EXEC[UTE]?
Personally I think that executing dynamic SQL in a stored procedure defeats the purpose of a stored procedure, perhaps doing this in the code would be better?
/HL
February 7, 2005 at 3:12 am
You need to put everything inside a string, including the cursor stuff and execute this. See http://www.sommarskog.se/dynamic_sql.html
May I add that dynamic sql + a cursor + a UDF are very likely to kill performance. Also, master.dbo.spt_values isn't fully documented. You'd better use your own number table.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 7, 2005 at 3:15 am
Hi Raju,
Not even attempting to work out what you are up to with this......
You need to build your sequel into a string, then EXEC it - or use sp_executesql.
I think your string build should look something like:-
DECLARE @SQL varchar(8000)
SET @SQL = 'SELECT INSERT INTO ' + @Tablename + '( AS stmt UNION ALL
SELECT
CHAR(9) + COL_NAME(OBJECT_ID(' + @Tablename + '), ORDINAL_POSITION) +
CASE WHEN ORDINAL_POSITION < (select count(*) from dbo.Split (' + @column + ',|) as int) THEN '','' ELSE '''' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ' + @Tablename + ' AND ORDINAL_POSITION <= (select count(*) from dbo.Split (' + @column + ',''|'') as int)
UNION ALL
SELECT '') Values (''
UNION ALL
SELECT
CASE c.DATA_TYPE
WHEN (''char'') THEN ''''+dbo.udf_get_string(' + @Tablecol + ',number+1,''|'')+''''
WHEN (''varchar'') THEN ''''+dbo.udf_get_string(' + @Tablecol + ',number+1,''|'')+''''
WHEN (''datetime'') THEN REPLACE(rtrim(ltrim(dbo.udf_get_string(' + @Tablecol + ',number+1,''|''))) , ''.'' , ''/'' )
----dbo.udf_get_string(@Tablecol,number+1,''|'')
ELSE
dbo.udf_get_string(' + @Tablecol + ',number+1,''|'')
end
+ CASE WHEN n.number < (select count(*) from dbo.Split (' + @column + ',''|'') as int) THEN '','' ELSE '''' END
FROM master.dbo.spt_values n
INNER JOIN INFORMATION_SCHEMA.COLUMNS c
ON c.TABLE_NAME = ' + @Tablename + ' AND c.ORDINAL_POSITION = n.number
WHERE n.type = ''P''
AND n.number > 0
AND n.number <= (select count(*) from dbo.Split (' + @column + ',''|'') as int)
UNION ALL
SELECT '''')'
Have fun
Steve
We need men who can dream of things that never were.
February 7, 2005 at 4:19 am
Thanx steve for response,
The out of the query is below
SELECT INSERT INTO A976( AS stmt UNION ALL
SELECT
CHAR(9) + COL_NAME(OBJECT_ID(A976), ORDINAL_POSITION) +
CASE WHEN ORDINAL_POSITION < (select count(*) from dbo.Split (ZS01 |0002 |03 |01783-03760 |31.12.9999|20.09.2001|0000029494 |,|) as int) THEN ',' ELSE '' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = A976 AND ORDINAL_POSITION <= (select count(*) from dbo.Split (ZS01 |0002 |03 |01783-03760 |31.12.9999|20.09.2001|0000029494 |,'|') as int)
UNION ALL
SELECT ') Values ('
UNION ALL
SELECT
CASE c.DATA_TYPE
WHEN ('char') THEN ''+dbo.udf_get_string(ZS01 |0002 |03 |01783-03760 |31.12.9999|20.09.2001|0000029494 |,number+1,'|')+''
WHEN ('varchar') THEN ''+dbo.udf_get_string(ZS01 |0002 |03 |01783-03760 |31.12.9999|20.09.2001|0000029494 |,number+1,'|')+''
WHEN ('datetime') THEN REPLACE(rtrim(ltrim(dbo.udf_get_string(ZS01 |0002 |03 |01783-03760 |31.12.9999|20.09.2001|0000029494 |,number+1,'|'))) , '.' , '/' )
----dbo.udf_get_string(@Tablecol,number+1,'|')
ELSE
dbo.udf_get_string(ZS01 |0002 |03 |01783-03760 |31.12.9999|20.09.2001|0000029494 |,number+1,'|')
end
+ CASE WHEN n.number < (select count(*) from dbo.Split (ZS01 |0002 |03 |01783-03760 |31.12.9999|20.09.2001|0000029494 |,'|') as int) THEN ',' ELSE '' END
FROM master.dbo.spt_values n
INNER JOIN INFORMATION_SCHEMA.COLUMNS c
ON c.TABLE_NAME = A976 AND c.ORDINAL_POSITION = n.number
WHERE n.type = 'P'
AND n.number > 0
AND n.number <= (select count(*) from dbo.Split (ZS01 |0002 |03 |01783-03760 |31.12.9999|20.09.2001|0000029494 |,'|') as int)
UNION ALL
SELECT '')
You can check this by
DECLARE @SQL varchar(8000)
Declare @Tablecol as varchar(8000),@column as varchar(8000),@Tablename as varchar(200),@sqlst as varchar(8000)
set @tablename='A976'
set @Tablecol='ZS01 |0002 |03 |01783-03760 |31.12.9999|20.09.2001|0000029494 |'
set @column='ZS01 |0002 |03 |01783-03760 |31.12.9999|20.09.2001|0000029494 |'
still have problem... coul not able to do it
After changing ZSO1.....0000029494 | as 'ZS01 |0002 |03 |01783-03760 |31.12.9999|20.09.2001|0000029494 |' manually getting error
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INSERT'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '|'.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ')'.
Pls. help me out.
Thanks
February 7, 2005 at 4:38 am
Hi again Raju,
Surely your udf should be passed a complete line as a varchar eg:
''+dbo.udf_get_string('ZS01|0002|03|01783-03760|31.12.9999|20.09.2001|0000029494|,number+1,|)+''
From what I can see you appear to just be missing the single quotes to create the string.......
Have fun
Steve
We need men who can dream of things that never were.
February 7, 2005 at 5:11 am
Thanks Steve... Thanks for Help
February 10, 2005 at 6:47 am
I'm going to side with everyone here about the "put it in a string" line of approach. However, allow me to add something. If you have a limited number of tables, it may be worth your while to write a separate SP for each table.
Yep, its lots of extra redundant code, I agree, but you get performance and there is precedent for that in Object Oriented design principles. I'd argue that the Command Behavioral Design Pattern has aspects that take this approach.
Just a thought.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply