August 16, 2013 at 11:28 pm
Hi,
Is there a way to call a SP for each row in a table, and to specify the value as a parameter? Like this....
Regards
Nicole 😀
--------------------------------------------------
SELECT CompanyName
FROM customers
-- This Result give to SP
Create PROCEDURE [dbo].[sp_Proceed]
@companyname nvarchar(500)
AS
BEGIN
DoAnyThing WITH this Parameter....
END
August 17, 2013 at 1:45 am
its better to use a table valued parameter and passed to SP
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 17, 2013 at 1:57 am
Hi,
i want to read @Filename from Table
----------------------------------------------
Create PROCEDURE [dbo].[sp_Proceed]
AS
BEGIN
-- i want to read @Filename from Table
DECLARE @sql NVARCHAR(4000) = 'BULK INSERT TblValues FROM ''' + @FileName
+ ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR ='''' )' ;
EXEC(@sql) ;
END
August 17, 2013 at 6:47 am
DECLARE cur CURSOR STATIC LOCAL FOR
SELECT FileName FROM tbl
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @filename
IF @@fetch_status <> 0
BREAK
EXEC LoadFile @filename
END
DEALLOCATE cur
There is little reason why you should not use a cursor this operation. Don't forget to include the keywords STATIC LOCAL.
Beware that the sp_ prefix is reversed for system procedures and you should not use it for your own code.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 17, 2013 at 8:25 pm
Slight changes in Erland script as cursor was not close in that script :
Here is the modified script:
DECLARE cur CURSOR STATIC LOCAL FOR
SELECT FileName FROM tbl
OPEN cur
FETCH NEXT FROM cur INTO @filename
WHILE 1 = 1
BEGIN
IF @@fetch_status <> 0
BREAK
EXEC LoadFile @filename
FETCH NEXT FROM cur INTO @filename
END
CLOSE cur
DEALLOCATE cur
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 18, 2013 at 2:56 am
kapil_kk (8/17/2013)
Slight changes in Erland script as cursor was not close in that script
DEALLOCATE implies CLOSE, so CLOSE it's not needed. You need CLOSE if you intend to re-open the cursor. I seem to recall that I wrote a stored procedure where I actually did this around 1997 or so.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 18, 2013 at 11:28 pm
Erland Sommarskog (8/18/2013)
kapil_kk (8/17/2013)
Slight changes in Erland script as cursor was not close in that scriptDEALLOCATE implies CLOSE, so CLOSE it's not needed. You need CLOSE if you intend to re-open the cursor. I seem to recall that I wrote a stored procedure where I actually did this around 1997 or so.
Thanks Erland for the info...
one more thing I saw in your code is that you write
FETCH cur INTO @filename
only once inside the loop....
Does this also eliminate writing the lines
FETCH NEXT FROM cur INTO @filename
before the loop and one time inside the loop as I did
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 19, 2013 at 1:09 am
kapil_kk (8/18/2013)
Thanks Erland for the info...
one more thing I saw in your code is that you write
FETCH cur INTO @filenameonly once inside the loop....
Yes. It is common to see code which goes:
DECLARE cur ...
OPEN cur ...
FETCH cur
WHILE @@fetch_status <> 0
BEGIN
-- Million lines of code
FETCH cur ...
END
This code is error-prone. You decide to add one more column to the result set, and you change the DECLARE and the first FETCH which are close to each other, but you forget the other. This gets even more confusing, as the cursor seems to work at the first quick test when you only have one row in the result set.
With my style with a single FETCH in the loop, you reduce the risk for this problem.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2013 at 2:00 am
Erland Sommarskog (8/19/2013)
kapil_kk (8/18/2013)
Thanks Erland for the info...
one more thing I saw in your code is that you write
FETCH cur INTO @filenameonly once inside the loop....
Yes. It is common to see code which goes:
DECLARE cur ...
OPEN cur ...
FETCH cur
WHILE @@fetch_status <> 0
BEGIN
-- Million lines of code
FETCH cur ...
END
This code is error-prone. You decide to add one more column to the result set, and you change the DECLARE and the first FETCH which are close to each other, but you forget the other. This gets even more confusing, as the cursor seems to work at the first quick test when you only have one row in the result set.
With my style with a single FETCH in the loop, you reduce the risk for this problem.
Thanks Erland for the information....
learn new thing today 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 19, 2013 at 2:07 am
Here is a non-cursor option, just for the sake of having an alternative to using a cursor:
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += 'EXEC LoadFile ' + QUOTENAME(FILENAME, '''') + ';' FROM tbl;
EXEC (@sql);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 19, 2013 at 3:24 am
opc.three (8/19/2013)
Here is a non-cursor option, just for the sake of having an alternative to using a cursor:
DECLARE @sql NVARCHAR(MAX) = N'';SELECT @sql += 'EXEC LoadFile ' + QUOTENAME(FILENAME, '''') + ';' FROM tbl;
EXEC (@sql);
This is not a good alternative, as the correct result of SELECT statement is undefined, and may not yield the expected result.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2013 at 7:29 am
Erland Sommarskog (8/19/2013)
opc.three (8/19/2013)
Here is a non-cursor option, just for the sake of having an alternative to using a cursor:
DECLARE @sql NVARCHAR(MAX) = N'';SELECT @sql += 'EXEC LoadFile ' + QUOTENAME(FILENAME, '''') + ';' FROM tbl;
EXEC (@sql);
This is not a good alternative, as the correct result of SELECT statement is undefined, and may not yield the expected result.
Can you please elaborate? I use this technique quite often and am interested to know what you mean by "as the correct result of SELECT statement is undefined". Is it a commentary on the technique in general or the specific application for the OP's problem case?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 19, 2013 at 4:03 pm
opc.three (8/19/2013)
Can you please elaborate? I use this technique quite often and am interested to know what you mean by "as the correct result of SELECT statement is undefined". Is it a commentary on the technique in general
It's a general comment. See http://support.microsoft.com/default.aspx?scid=287515.
Use FOR XML PATH('') instead, which is well-defined.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2013 at 9:54 pm
Erland Sommarskog (8/19/2013)
opc.three (8/19/2013)
Can you please elaborate? I use this technique quite often and am interested to know what you mean by "as the correct result of SELECT statement is undefined". Is it a commentary on the technique in generalIt's a general comment. See http://support.microsoft.com/default.aspx?scid=287515.
Use FOR XML PATH('') instead, which is well-defined.
First off let me thank you for compelling me to explore this issue again. I had done similar research a long time ago but things do tend to change within the engine and so I was happy to have a reason to do the research again.
Despite any interpretation of "The correct behavior for an aggregate concatenation query is undefined." that might imply that the overall technique provides unstable behavior, when used consistently as shown in the Workaround section of the article the behavior proves to be predictable across many versions. The article has not been updated since the SQL 2000 days per the Applies To section but the behavior in the article holds true on SQL 2005, SQL 2008 R2, SQL 2012 and SQL 2014 CTP1.
The comment in the article about the ANSI-92 specification requiring that the SELECT-column-list should contain all columns used in the ORDER BY and that an expression used in an ORDER BY cannot ever be represented in the SELECT-column-list is the key indicator in my eyes as to why the repro-code continues to be a problem for the query optimizer, and conversely why the Workaround code continues to work as expected. Even when a SELECT-column made from an expression is referenced in the ORDER BY using the chosen SELECT-column-alias the behavior remains undefined, however when if no ORDER BY is provided or when only columns are referenced in the ORDER BY then all is well.
For completeness here is an XML option that avoids the use of a cursor and could be considered acceptable by more people than the aggregate concatenation technique if it were held up against any "is documented behavior" criteria. That said, I would not hesitate to employ the aggregate concatenation technique I showed in the form I showed, or in the form showed in the article's Workaround section.
USE YourDatabaseName
go
IF OBJECT_ID('dbo.MyFileTable') IS NOT NULL
DROP TABLE dbo.MyFileTable
go
CREATE TABLE dbo.MyFileTable
(
MyFileName VARCHAR(500)
)
go
INSERT INTO dbo.MyFileTable
(MyFileName)
VALUES ('File1.txt'),
('File2.txt'),
('File1&File2Combined.txt');
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += 'EXEC LoadFile ' + QUOTENAME(MyFileName, '''') + ';'
FROM dbo.MyFileTable
ORDER BY MyFileName;
PRINT @sql;
SET @sql = ( SELECT N'EXEC LoadFile ' + QUOTENAME(MyFileName, '''') + ';' AS n
FROM dbo.MyFileTable
ORDER BY MyFileName
FOR XML PATH(''), TYPE ).value('.[1]', 'nvarchar(max)');
PRINT @sql;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 20, 2013 at 3:09 pm
opc.three (8/19/2013)
That said, I would not hesitate to employ the aggregate concatenation technique I showed in the form I showed, or in the form showed in the article's Workaround section.
I've stopped doing that long ago. I simply don't like playing with fire.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply