June 26, 2014 at 7:37 am
Hello, I currently have a process that has a cursor. It takes data and executes a stored procedure with parameters.
declare @tmpmsg varchar(max)
declare @tmpmsgprefix varchar(max)
declare @CMS varchar(20)
create table #tmpIntegrity(matternum varchar(10),ClientName varchar(20))
insert into #tmpIntegrity(matternum,ClientName)
select a.AccountNum,a.ClientName from ATable a
DECLARE Criteria_C2 CURSOR FOR select distinct ClientName from #tmpIntegrity order by ClientName
OPEN Criteria_C2
FETCH NEXT FROM Criteria_C2 into @CMS
While @@Fetch_Status= 0
Begin
set @tmpmsg=null
set @tmpmsgprefix='The following ' + @CMS + ' accounts have A1 value and a blank A2 field. Accounts: '
select @tmpmsg=coalesce(@tmpmsg + ', ','') + t.matternum
from #tmpIntegrity t
where t.ClientName=@cms
if @tmpmsg is not null
begin
execute usp_IMessage 832,101,@tmpmsgprefix,@tmpmsg,','
end
FETCH NEXT FROM Criteria_C2 into @CMS
end
CLOSE Criteria_C2
Deallocate Criteria_C2
I have come up with the following to create the message to be pushed, however I have two problems.
CREATE TABLE #MyClients (Client VARCHAR(20), Account VARCHAR(20));
INSERT INTO #MyClients (Client, Account) VALUES
('Client1', 'Ac1'),('Client1', 'Ac2'),('Client1', 'Ac3'),('Client1', 'Ac4'),
('Client2', 'Ac1'),('Client2', 'Ac2'),('Client2', 'Ac3'),
('Client3', 'Ac1'),('Client3', 'Ac2'),('Client3', 'Ac3'),
('Client4', NULL);
-- Thanks to Paul White for pointers in the logic from
-- http://www.sqlservercentral.com/Forums/Topic872409-392-1.aspx
SELECT 'The following ' + Clients.Client + ' accounts have A1 value and a blank A2 field. Accounts: '
+ Accts.AccountList
FROM (
-- Get distinct client list
SELECT Client
FROM #MyClients
GROUP BY Client
) AS Clients
CROSS APPLY
(
-- Concatenate the accoutns
SELECT Account + ','
FROM #MyClients b
WHERE Clients.Client = b.Client
FOR XML PATH('')
) AS Accts (AccountList);
DROP TABLE #MyClients;
First problem:
Output from code:
The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4,
The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,
The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,
The following Client4 accounts have A1 value and a blank A2 field. Accounts:
Desired output (no trailing comma):
The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4
The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3
The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3
The following Client4 accounts have A1 value and a blank A2 field. Accounts:
Next, how do I call the stored procedure without doing it RBAR? Is that possible?
execute usp_IMessage 832,101,@tmpmsgprefix,@tmpmsg,','
Hope I made this clear enough.
Thank you,
djj
June 26, 2014 at 10:07 am
You can try this to get results without trailing comma.
CREATE TABLE #MyClients (Client VARCHAR(20), Account VARCHAR(20));
INSERT INTO #MyClients (Client, Account) VALUES
('Client1', 'Ac1'),('Client1', 'Ac2'),('Client1', 'Ac3'),('Client1', 'Ac4'),
('Client2', 'Ac1'),('Client2', 'Ac2'),('Client2', 'Ac3'),
('Client3', 'Ac1'),('Client3', 'Ac2'),('Client3', 'Ac3'),
('Client4', NULL);
-- Thanks to Paul White for pointers in the logic from
-- http://www.sqlservercentral.com/Forums/Topic872409-392-1.aspx
SELECT 'The following ' + Clients.Client + ' accounts have A1 value and a blank A2 field. Accounts: '
+ Accts.AccountList
FROM (
-- Get distinct client list
SELECT Client
FROM #MyClients
GROUP BY Client
) AS Clients
CROSS APPLY
(
-- Concatenate the accoutns
select isnull(stuff((SELECT ', '+Account
FROM #MyClients b
WHERE Clients.Client = b.Client
FOR XML PATH('')),1,1,''),'')
) AS Accts (AccountList);
DROP TABLE #MyClients;
For calling the proc usp_IMessage without a cursor I would think you need to change the logic in the proc( if you are able to) to accept and process as table parameter.
June 26, 2014 at 11:22 am
Create a function to execute sql query and then cross apply with TABLE1 which is the output of the result from the query modified by Bob.
create function dbo.fn_execsqlquery
(@str_SQLQuerystring nvarchar(1000))
returns int
as
begin
DECLARE @int_Result int
, @str_SQLQuerystring_tobe NVARCHAR(4000);
SET @str_SQLQuerystring_tobe = 'EXEC usp_IMessage 832,101,' + @str_SQLQuerystring;
EXEC @int_Result= sp_executesql @str_SQLQuerystring_tobe
-- @int_Result 0 for success, non zero for failure
return @int_Result
end
GO
SELECT *
FROM TABLE1 t
CROSS APPLY dbo.fn_execsqlquery(t.SQLQuerystring)
Update: before trying the code, format the variable @str_SQLQuerystring_tobe inside function appropriately as it is string.
June 26, 2014 at 11:25 am
Thank you Bob and Suneel. I have Bob's suggestion working so now to try the function.
Thanks again,
djj
June 26, 2014 at 5:30 pm
suneel kamavaram (6/26/2014)
Create a function to execute sql query and then cross apply with TABLE1 which is the output of the result from the query modified by Bob.
create function dbo.fn_execsqlquery(@str_SQLQuerystring nvarchar(1000))
returns int
as
begin
DECLARE @int_Result int
, @str_SQLQuerystring_tobe NVARCHAR(4000);
SET @str_SQLQuerystring_tobe = 'EXEC usp_IMessage 832,101,' + @str_SQLQuerystring;
EXEC @int_Result= sp_executesql @str_SQLQuerystring_tobe
-- @int_Result 0 for success, non zero for failure
return @int_Result
end
GO
SELECT *
FROM TABLE1 t
CROSS APPLY dbo.fn_execsqlquery(t.SQLQuerystring)
Update: before trying the code, format the variable @str_SQLQuerystring_tobe inside function appropriately as it is string.
Encapsulation of code is a great idea but I'd avoid non-iTVF functions (any function that requires BEGIN/END in it) like the plague. Please see the following article for why and, possibly, how.
http://www.sqlservercentral.com/articles/T-SQL/91724/
I'm also pretty sure that you can't execute dynamic SQL inside a function like that but haven't tried since the year 2005 so could be wrong there.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2014 at 4:35 am
Used Bob's method with the STUFF then used a While loop and ROW_NUMBER to control which record. Still RBAR but it works.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply