Hello,
I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail).
How can I process result rows inside a stored procedure?
This is what I have so far: CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert]
@serviceDetailID int,
@comment varchar(500),
@commentDate DateTime,
@commentAuthor varchar(100)
AS
BEGIN
DECLARE @serviceID int
DECLARE @p_recipients varchar(8000)
DECLARE @p_message varchar(8000)
DECLARE @p_subject varchar(100)
/* Grab the Service_id from underlying Service_Detail_id*/
SELECT @serviceID = Service_id FROM lm_Service_Detail WHERE Service_Detail_id = @serviceDetailID
/* Get email addresses of Service Responsible Parties */
SELECT DISTINCT dbo.lm_Responsible_Party.Email
FROM dbo.lm_Service_Detail INNER JOIN
dbo.lm_Service_Filing_Type ON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_id INNER JOIN
dbo.lm_Responsible_Party_Filing_Type ON
dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_id INNER JOIN
dbo.lm_Responsible_Party ON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @serviceDetailID)
/* Build message */
SET @p_subject = "KLM - Service ID: " + CAST(@serviceID AS varchar(4))
SET @p_recipients = "" /*need string of addresses*/
SET @p_message = @p_message + "Service Detail ID: " + CAST(@serviceDetailID AS varchar(4)) + char(13)
SET @p_message = @p_message + "Comment Date: " + CAST(@commentDate As varchar(25)) + char(13)
SET @p_message = @p_message + "Comment Author: " + @commentAuthor + char(13)
SET @p_message = @p_message + "Comment: " + @comment + char(13)
Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
hi all, i'm wondering if i can use one stored procedure in too cases, this is the senario: i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user if there a way to use if and else to make this happen this is what i did so far:
CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as if @indexType = 1
begin
SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin) end else begin SELECT * FROM table WHERE user = @user end GO
I want to execute some insert statements within a stored procedure and commit those changes regardless of any raiseerror that occurs later in the stored procedure. My difficulty is that I am forced to use raiseerror with severity 16 in order to send a message through the powerbuilder application interface (compiled vendor code). I have tried save points but can't get that to save my insert and still present an error to the user about something else that happens later.
here is an example
BEGIN procedure
Insert something and save it even if error is raised below
RaiseError('you made a mistake and need to do this.',16,-1)
Hi folks I nead to call a stored procedure in a where statemene, but MSSQL dont like that.
My problem is that the StoredProcedure is calling itself recursive and therfore its impossible to add the code as a standard SELECT statement. Here is the code
ALTER PROCEDURE dbo.advsp_FilterRecordRights @RequesterGUID Char(20), @EntityGUID Char(20) AS BEGIN if not Exists(Select *
from GUIDRightsH where GUIDRightsH.EntityGUID = @EntityGUID and
GUIDRightsH.RequesterGUID = @RequesterGUID and GUIDRightsH.RecProp <> 0) Begin
Return 1 end
if not Exists(Select *
from UsergroupMembers where UserGroupMembers.UsergroupGUID = @RequesterGUID and dbo.advsp_FilterRecordRights(UserGroupMembers.UserGUID,@EntityGUID) = 1) Begin
Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure For example:String1 = "Here is some data. lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though. So I would want string2 to look like thisstring2 = "Here<font color = "#00FF00">'s</font> <strike>is</strike> some data. <font color = "#00FF00">L</font>ets type some <strike>more</strike> data <font color = "#00FF00">here</font>" Is there an way to accomplish this inside a stored procedure?
This example is working: Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY LastActivityDate DESC) AS Row, UserId, UserName FROM aspnet_Users) SELECT UserId, UserName FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND and this one doesn't work:USE [xx]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[battle_Paging]@PagerIndex INT,@ShowMembers INT ASBEGINDeclare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY aspnet_Users.LastActivityDate DESC) AS Row, aspnet_Users.UserId, aspnet_Users.UserName, mb_personal.Avatar FROM aspnet_Users LEFT JOIN mb_personal ON (mb_personal.UserID=aspnet_Users.UserId) SELECT UserId, UserName, Avatar FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND Error:Msg 156, Level 15, State 1, Procedure battle_Paging, Line 18Incorrect syntax near the keyword 'SELECT'. I try to join in the table mb_personal here. Some help would be very appreciated! Thanks.
I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors. Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?
ALTER PROCEDURE dbo.spGetNextPageRecords
( @pageSize int, @previousMaxId int
)
AS /* SET NOCOUNT ON */ SELECT Top @pageSize ProductId, ProductName FROM Products WHERE (ProductID > @previousMaxId) order by ProductId RETURN
CREATE PROCEDURE SelectCostumers @name varchar(100) Declare @SQL = "SELECT Id, Name FROM Costumers" AS IF (@name IS NULL) @SQL ELSE @SQL += "WHERE Name LIKE @name"
See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.
I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:
SELECT B.COMPONENT, TOT_OPTIONS_EXCHANGED = SUM(A.UNITS) FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B WHERE B.COMPONENTID = A.COMPONENTID GROUP BY B.COMPONENT
GO
Any help getting this to run correctly would be appreciated.
I have a cursor inside a stored procedure that should identify rows based on certain criteria and update the columns on those records.
While I try to do so, the cursor goes into an infinite loop although I have only 1 record that matches my criteria. I think that the transaction is not commited and the cursor is picking up the same record for update.
Here is my sample record in table :tblMsg SenderRef <space> MsgStatusId <space> MsgType <space>Groupid 1281341<space> 1 <space>KBC-NON-ETC-MATCHED-BLIM <space>191902
DECLARE @blimGroupId AS INT DECLARE @admSenderRef AS VARCHAR(50)
DECLARE admCursor CURSOR FOR SELECT senderref FROMtblMsg WHERE msgstatusid = 18 AND msgtype = 'KBC-RCVD-ADM' FOR UPDATE
OPEN admCursor
FETCH NEXT FROM admCursor INTO @admSenderRef
WHILE @@FETCH_STATUS = 0 BEGIN --FIND CORRESPONDING BLIM RECORD AND GRAB ITS GROUPID TO UPDATE THE ADM SELECT @blimGroupId = GroupID FROM tblMsg WHERE msgType = 'KBC-NON-ETC-MATCHED-BLIM' AND SenderRef = @admSenderRef PRINT 'AFTER SELECT INSIDE WHILE: Senderref- >' + @admSenderRef; --UPDATE THE ADM RECORD WITH THE GROUPID AND MSGSTATUS = 3 BEGIN UPDATE tblMsg SET MsgStatusId = 3, GroupId = @blimGroupId WHERE CURRENT OF admCursor END PRINT 'AFTER UPDATE INSIDE WHILE'; END
CLOSE admCursor DEALLOCATEadmCursor
The update statement was included beteween a BEGIN TRAN and END TRAN. But no joy.
Okay, so I have a problem and I would be REALLY grateful for anyassistance anyone can offer because I have found little or no help onthe web anywhere.I want to access and do joins between tables in two different SQL db'son the same server. Heres what Im dealing with.In one database resides all of my security features for our clients,where it decides who can login, etc etc....In another database, I need to cross reference with a few fields in mysecurity db.See the issue Im running into here is that because the way the peoplehave their databases set up for different products, I would normallyhave to put these tables with security features in every database...which is horrible, because every time I do an update I would have todo it in 12 different places. Thats not efficient at all.So I thought if I had one central DB, where all security features arecontrolled from, that would be perfect... now the issue is crossreferencing and doing joins with other tables that ARENT in the samedb....have I lost you yet?I appreciate all of your help!THANKS!!
I have to write a stored procedure what repair a table. It have to delete lost rows before make relation to itself (PK column is 'Kw_KeywordID', FK column is 'Kw_ParentID'). I do not found the way to write this procedure to can create statement from parameter of the procedure. I would like to pass the table name, but I receive error if the query like 'Select ... Form @Table ....'. If the parameter is in the 'WHERE', nothing problem.
My procedure is: CREATE PROCEDURE sp_Repair_IS_KW_AbtKz176 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
declare @OrphanRowCount int select @OrphanRowCount = 1
while(@OrphanRowCount > 0) begin DECLARE KWCursor CURSOR FOR SELECT count(*) AS cRowCount FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176);
OPEN KWCursor; FETCH NEXT FROM KWCursor INTO @OrphanRowCount CLOSE KWCursor DEALLOCATE KWCursor
IF (@@FETCH_STATUS = 0) AND (@OrphanRowCount > 0) BEGIN exec('DELETE FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176)') END end END GO
How can I run fully parameterized queries from an SP. I can make it only like exec('DELETE FROM' + @TableName + ...)
Dear all,i'm facing a problem with my storedprocedure which happened when i ran my web application and reach to the point where my class invoke this storedprocedure,my SP contains a cursor that built his sql according to certain condition, so i put the "SET @cur Cursor For....." inside the if block (definitely i've declared it under AS keyword directly) and this SP is working well inside sql server(I've tested it), BUT when my ASP.net code invoke this SP it gives me the following error : "The Variable @cur does not currently have a cursor allocated to it" repeated as much as there are IF clauses in my SP,Please Help.Regards,
I want to use a stored procedure inside Sql Server from my aspx page so that the data entered in the form goes to the database after submit.
My stored procedure (inside the SQL Server) inserts several fields in a table and it returns two variables. What code I need to write in order to pass the data from the form to the stored procedure inside the Sql Server? And to store the two returned values?
Hi, I'm new to ASP.NET 2.0. I have a sqldatasource and a formview controls on a web page and inside the formview control I have two textboxes. When I click the UPDATE button in the formview, I'd like to be able to retrieve the values of the textboxes inside the formview control and pass these values to a 'Update' SQL stored procedure defined in the Sqldatasource. Does anyone know how I can do this? Hope my question is clear. Thanks in advance. hakl
Hi, I have two tables A and B. In table A i have three columns called empid, empname and empsalary where empid is an identity column. Table A has some records filled in it. Table B has the same schema except the fact that the empid is not an identity column in table B. Table B does not contain any rows initially. All other aspects remain the same as that of table A. Now i am going to delete some records in table A based on the empid. When i delete the records in table A based on empid the deleted records should be inserted into table B with the same empid. I need to accomplish these two tasks in a single stored procedure. How to do it? I need the entire code for the stored procedure. Please help me. I am trying for the past 4 days. Thanx in Advance
I need to run a sequel statement in a stored procedure and get if it found any records because the next statements depend on if records were found or not. How do I do this?
In the below procedure definition, i need to find a way parse the definition and get the list of places where the where clause is being used.
SELECT DISTINCT FC.CASE_ID, UPPER(FC.CASE_NUMBER) AS CASE_NUMBER, UPPER(REPLACE(FC.CASE_SHORT_TITLE,CHAR(13)+CHAR(10),'')) AS CASE_SHORT_TITLE, FC.CASE_STATUS_DESCR, FC.CASE_TYP_DESC, FC.CASE_SUB_TYP_DESC,
[Code] ....
In the above query there are more than one places and the where clause may not have the same string the where clause it can be with a space between the "=" and the value in single quotes.
Result set should be in the below format:
TABLE NAME Column Name VALUE CFG_ELEMENTS ELEMENT_NAME REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID
I was wondering if anyone can explain the positives and negatives of using a single stored procedure that contains one or more distinct queries. I know there are problems with dynamic SQL but I am not proficient enough to know whether this falls under that umbrella.
For clarification, what I am referring to is this: In a single stored procedure, I have a parameter called Query_ID that is used to identify which query in the sproc that I want to execute. Then from my ASP page, I simply pass the appropriate value for Query_ID. So:
IF @QUERY_ID = 1 BEGIN SELECT [whatever] FROM [tbl1] WHERE [conditions] GROUP BY [something] ORDER BY [somethingelse] END ELSE IF @QUERY_ID = 2 BEGIN SELECT [whatever] FROM [tbl2] WHERE [conditions] GROUP BY [something] ORDER BY [somethingelse] END END
Hi, Please help me in this problem... i am new to sql server.. i am using sql server version 8...(doesnot support function with retun values..) so i have created a procedure... -----------procedure------------------(to find next monday after 6 months)------------------- [code] create proc next_Monday ( @myDate DATETIME ) as BEGIN set @myDate = dateadd(mm, 6, @myDate) while datepart(dw,@myDate) <> 2 begin set @myDate = dateadd(dd, 1, @myDate) end select @myDate end go [/code] -------------------------------------------------------- i can able to execute this procedure separately.... working well... but don't know how to call it inside another query.... the following throws error.... select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster please help me... how to fix this problem...
I would like to use MatLab built-in statistical functions (beta, gamma, normal, etc.) from inside a SQLServer stored proceudre. Does anyone know if possible? (Of course, If so, where can I get documentation for doing this?)
Hello evry1. i m new to SQL Server2000. plzz tell me where i m wrong in this SP . this procedure is not giving any error but it is not showing any result. plzz help me n give me any idea if u know how i can solve my problem best regards sadaf n here is the procedure
begin select * from [profile] where [user_name] like '%@user_name1%' and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@gender1 is not null and len(@gender1) > 0) begin select * from [profile] where gender = @gender1 and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@email_address1 is not null and len(@email_address1) > 0) begin select * from [profile] where [email-address] like '%@email_address1%' and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@relegion1 is not null and len(@relegion1) > 0) begin select * from [profile] where relegion = @relegion1 and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@political_view1 is not null and len(@political_view1) > 0) begin select * from [profile] where political_view = @political_view1 and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@passion1 is not null and len(@passion1) > 0) begin select * from [profile] where passion like '%@passion1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@sports1 is not null and len(@sports1) > 0) begin select * from [profile] where sports like '%@sports1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@activities1 is not null and len(@activities1) > 0) begin select * from [profile] where activities like '%@activities%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@books1 is not null and len(@books1) > 0) begin select * from [profile] where books like '%books1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@music1 is not null and len(@music1) > 0) begin select * from [profile] where music like '%@music%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@tv_shows1 is not null and len(@tv_shows1) > 0) begin select * from [profile] where tv_shows like '%@tv_shows1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@movies1 is not null and len(@movies1) > 0) begin select * from [profile] where movies like '%@movies1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@cuisines1 is not null and len(@cuisines1) > 0) begin select * from [profile] where cuisines like '%@cuisines1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@intrested_in1 is not null and len(@intrested_in1) > 0) begin select * from [profile] where intrested_in = @intrested_in1 and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@education1 is not null and len(@education1) > 0) begin select * from [profile] where education like'%@education1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@college_university1 is not null and len(@college_university1) > 0) begin select * from [profile] where institution like '%@college_university1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@occupation1 is not null and len(@occupation1) > 0) begin select * from [profile] where occupation like '%@occupation1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@industry1 is not null and len(@industry1) > 0) begin select * from [profile] where industry like '%@industry1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@job_desc1 is not null and len(@job_desc1) > 0) begin select * from [profile] where job_desc like '%@job_desc1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@career_intrest1 is not null and len(@career_intrest1) > 0) begin select * from [profile] where career_intrest like '%@career_intrest1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end GO
