September 25, 2015 at 12:26 am
Hi All,
I have a stored proc I want to convert it to either a Normal Query using A while loop or a set based operation/recursive cte as I want to run it for multiple CompanyNames. I get the error message as An INSERT EXEC statement cannot be nested when I execute if for Multiple Companies using another Cursor
If I convert it to a Function I get the below error message
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
Please help me in converting this query to a normal query or let me know if there is any change which need to done to work with multiple companynames.
CREATE PROC [dbo].[USPT] @CompanyName varchar(50),@tablename varchar(50)
AS
BEGIN
-- EXEC [USPT] 'xyz corp','Sales Header'
DECLARE @str1 VARCHAR (MAX)
set @str1 = '
DECLARE @No VARCHAR (MAX)
DECLARE @STR VARCHAR (MAX)
DECLARE @CR Table ([No] varchar (200), Name Varchar (300), CRStatus Int)
DECLARE CR_Cur CURSOR FOR
select [No_] FROM ['+@CompanyName+'$'+@tablename+'] WHERE [CR Blocked]=1 and [Document Type] in (1,4,5)
OPEN CR_Cur
FETCH NEXT FROM CR_Cur INTO @No
WHILE @@FETCH_STATUS = 0
BEGIN
set @STR = (''select distinct No_, Name,[CR Status] from ['+@CompanyName+'$'+'CR Log] where No_ = ''''''+@No+'''''''')
Insert into @CR
EXEC (@str)
FETCH NEXT FROM CR_Cur INTO @No
end
;with cte as
(
SELECT No AS No_,Name, ''Sales Header'' as type FRom @CR WHERE CRStatus = 1
except
SELECT No AS No_,Name, ''Sales Header'' as type FRom @CR WHERE CRStatus = 2)
select '''+@CompanyName+''' as Company, count(*) as SalesCount from cte
CLOSE CR_Cur
DEALLOCATE CR_Cur'
EXEC (@str1)
Regards
S
September 25, 2015 at 7:22 am
ksrikanth77 (9/25/2015)
Hi All,I have a stored proc I want to convert it to either a Normal Query using A while loop or a set based operation/recursive cte as I want to run it for multiple CompanyNames. I get the error message as An INSERT EXEC statement cannot be nested when I execute if for Multiple Companies using another Cursor
If I convert it to a Function I get the below error message
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
Please help me in converting this query to a normal query or let me know if there is any change which need to done to work with multiple companynames.
CREATE PROC [dbo].[USPT] @CompanyName varchar(50),@tablename varchar(50)
AS
BEGIN
-- EXEC [USPT] 'xyz corp','Sales Header'
DECLARE @str1 VARCHAR (MAX)
set @str1 = '
DECLARE @No VARCHAR (MAX)
DECLARE @STR VARCHAR (MAX)
DECLARE @CR Table ([No] varchar (200), Name Varchar (300), CRStatus Int)
DECLARE CR_Cur CURSOR FOR
select [No_] FROM ['+@CompanyName+'$'+@tablename+'] WHERE [CR Blocked]=1 and [Document Type] in (1,4,5)
OPEN CR_Cur
FETCH NEXT FROM CR_Cur INTO @No
WHILE @@FETCH_STATUS = 0
BEGIN
set @STR = (''select distinct No_, Name,[CR Status] from ['+@CompanyName+'$'+'CR Log] where No_ = ''''''+@No+'''''''')
Insert into @CR
EXEC (@str)
FETCH NEXT FROM CR_Cur INTO @No
end
;with cte as
(
SELECT No AS No_,Name, ''Sales Header'' as type FRom @CR WHERE CRStatus = 1
except
SELECT No AS No_,Name, ''Sales Header'' as type FRom @CR WHERE CRStatus = 2)
select '''+@CompanyName+''' as Company, count(*) as SalesCount from cte
CLOSE CR_Cur
DEALLOCATE CR_Cur'
EXEC (@str1)
Regards
S
Kudos for wanting to get this into a set based type of operation. Don't ever bother converting a cursor into a while loop. It will still be RBAR and the performance difference will be negligible. Wanting to do the same code for a number of companies is not what a recursive cte would be for either.
We can help you with that but we need quite a bit more information first. This link has an excellent example of the types of things you should post. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
I have to admit I am pretty scared that you are passing the table name to a procedure like this and the object names are just awful. This procedure needs a complete rewrite so along with the details it would be helpful to know how much flexibility you have in redesigning this procedure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 25, 2015 at 7:37 am
You make it very difficult to help you. You only post a part of your code which doesn't seem to require a cursor or recursive query at all.
You might want to change your table variable to a temp table. If you post the full code (including the other cursor) with sample data and expected results, we could give a better answer. Meanwhile, here's a shot in the dark.
Creating tables for each company is a really bad design. You should have a single table with a Company column to remove all this dynamic code. That's what got you in this cursor problem in the first place.
CREATE PROC [dbo].[USPT] @CompanyName varchar(50),@tablename varchar(50)
AS
--DECLARE @CompanyName varchar(50) = 'xyz corp', @tablename varchar(50) = 'Sales Header'
-- EXEC [USPT] 'xyz corp','Sales Header'
DECLARE @STR NVARCHAR (1000);
set @STR = ('select @CompanyName as Company, ' + CHAR(10) +
' count(*) as SalesCount ' + CHAR(10) +
'from (select No_, Name ' + CHAR(10) +
' from ' + QUOTENAME( @CompanyName+'$CR Log') + CHAR(10) +
' where No_ IN (SELECT [No_] ' + CHAR(10) +
' FROM ' + QUOTENAME( @CompanyName+'$'+@tablename) + CHAR(10) +
' WHERE [CR Blocked]=1 ' + CHAR(10) +
' AND [Document Type] in (1,4,5))' + CHAR(10) +
' AND [CR Status] = 1' + CHAR(10) +
' EXCEPT' + CHAR(10) +
' select No_, Name ' + CHAR(10) +
' from ' + QUOTENAME( @CompanyName+'$CR Log') + CHAR(10) +
' where No_ IN (SELECT [No_] ' + CHAR(10) +
' FROM ' + QUOTENAME( @CompanyName+'$'+@tablename) + CHAR(10) +
' WHERE [CR Blocked]=1 ' + CHAR(10) +
' AND [Document Type] in (1,4,5))' + CHAR(10) +
' AND [CR Status] = 2)x;');
EXEC sp_executesql @STR, N'@CompanyName varchar(50)', @CompanyName
September 25, 2015 at 8:52 am
Thank you for providing me the reply and it works perfect when I am passing the single company. Will it be possible to perform the same for the All the companies which are in Company Table. Select Name from [Company]. Could you please help me.
September 25, 2015 at 9:14 am
Luis' code is wonderful but it's missing a very important part. Although the use of QUOTENAME certainly helps, you really should delouse the inputs for @CompanyName and @TableName prior to executing the Dynamic SQL just to make it absolutely impossible for SQL Injection to occur.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 28, 2015 at 1:05 pm
NOT EXISTS might also be an option here.
I don't see anything in the original code that indicates that Blocked or Doc Type must match in the CR Log, but naturally adjust the code as needed to match your requirements.
CREATE PROCEDURE [dbo].[USPT]
@CompanyName varchar(50),
@tablename varchar(50),
@print_sql bit = 0,
@exec_sql bit = 1
AS
SET NOCOUNT ON;
-- EXEC [USPT] 'xyz corp','Sales Header',1,0
DECLARE @str1 varchar(MAX)
SET @CompanyName = PARSENAME(@CompanyName, 1)
SET @tablename = PARSENAME(@tablename, 1)
--validate input param values
IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE name LIKE @CompanyName + '%' AND type IN ('IF', 'TF', 'U', 'V'))
BEGIN
RAISERROR('Company name is not valid, invalid access attempt was logged!', 16, 1)
--log access attempt
RETURN -1
END --IF
IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE name = @tablename AND type IN ('IF', 'TF', 'U', 'V'))
BEGIN
RAISERROR('Table name is not valid, invalid access attempt was logged!', 16, 1)
--log access attempt
RETURN -2
END --IF
SET @str1 = '
SELECT @CompanyName AS Company, COUNT(*) as SalesCount
FROM ['+@CompanyName+'$'+@tablename+'] tbl
WHERE
tbl.[CR Blocked] = 1 AND
tbl.[Document Type] IN (1,4,5) AND
NOT EXISTS(SELECT 1 FROM ['+@CompanyName+'$CR Log] WHERE CRStatus = 2 AND No_ = tbl.No_)
'
IF @print_sql = 1
PRINT @str1
IF @exec_sql = 1
EXEC (@str1)
GO --end of proc
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply