Send GUID ARRay To The Stored Procedure
Apr 28, 2008Hi there,
My requirement is to send more than one GUID to the stored procedure.
How can I do that? If you can give me an example that will be great.
Kind regards,
Ricky
ADVERTISEMENT
How Would I Send A String Array As A Integer Array?
Jun 25, 2007I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type.
What would I need to do to convert it to an Integer array?
@OfficerIDs as varchar(200)
Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs)
Thanks
How To Pass A GUID String To A Varchar In SQL Stored Procedure
Aug 26, 2007In my .NET app I have a search user control. The search control allows the user to pick a series of different data elements in order to further refine your display results. Typically I store the values select in a VarChar(5000) field in the DB. One of the items I recently incorporated into the search tool is a userID (GUID) of the person handling the customer. When I go to pass the selected value to the stored proc
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = strCriteria;
I get: Failed to convert parameter value from a String to a Guid.
Ugh! It's a string, dummy!!! I have even tried:
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString();
and
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString("B");
but to no avail. How can I pass this to the stored proc without regard for it being a GUID in my app?
Array In A Stored Procedure
Jun 1, 2006Can any one help me with a sample code, which can take an array of
elements as one of it's parameters and get the value inserted into a table in a stored procedure.
Thanks in advance
vnswathi.
Passing Array With Ids To Stored Procedure
Apr 1, 2004I want to pass and array of ids to a procedure for inserting i a relation table.
I found some examples in other posts, but had problems getting them to work.
I just want to pass a parameter with value like '1,45,89' to the procedure, then loop through it to insert the relations.
(I´m using sql server 2000), had some problem with examples with strpos then.
Any hints ?
peace.
How To Impliment Array In Stored Procedure?
Apr 8, 2005i doing an online shop project which have an shoppingcart and it stored database.
and i have the situation like this.
the products have properties such as size, color . and customers
can buy a product with particular size or color. and i have
the shopping cart table structure and data like following
Id(primary key) CartId
productId size
color quantity
1
1
1
S red
10
2
1
1
S black
2
3
1
1
S blue
3
4
1
1
M red
5
5
1
1
L
blue 2
all the data above is i image the customer may inputed. And my
problem is how to use an stored procedure to updata above record
when a customer buy the same product which is one of the product from
above(have same productId, size, color)
and i try to use the following code but it didn't work
<code>
create procedure shoppingcart_add_item
( @cartId int,
@productId int,
@size nvarchar(20),
@color nvarchar(20),
@quantity int
)
AS
DECLARE @countproduct
DECLARE @oldsize
DECLARE @oldcolor
select @countproduct=count(productId) FROM shoppingcart WHERE productId=@productId AND cartId=@cartId
select @oldsize=size,@oldcolor=color FROM shoppingcart WHERE productId=@productId
IF @CountItems > 0 and @oldsize = @size and @oldcolor = @color
UPDATE
ShoppingCart
SET
Quantity = (@Quantity + ShoppingCart.Quantity)
WHERE
ProductId = @ProductId
AND
CartId = @CartId
ELSE /* New entry for this Cart. Add a new record */
INSERT INTO ShoppingCart
(
CartId,
ProductId,
Quantity,
color,
size
)
VALUES
(
@CartId,
@ProductId,
@Quantity,
@size,
@color
)
</CODE>
and the result from this stored procedure is not what i want, what i
try to say is can i stored all the size and color in @oldsize and
@oldcolor array. then loop through the array to get the one i
want??????
somebody get any idea??? or don't know what i am talking about?
How Array Will Pass To Stored Procedure
May 7, 2001I have a two dimensional array in Front end (As for example Array contains 20 ECode and EmployeeName). I have a Stored Proc. where i have written a statement for inserting theses value in a table. so how i will pass this array to SP. Pls. give exmp. for Front end and SP also.
View 3 Replies View RelatedHow To Create An Array In A Stored Procedure
Aug 8, 2000I just want to Know how to create an Array in a stored procedure. Please can you give the syntax and any Example.
View 1 Replies View RelatedArray Of Values Using Stored Procedure
Feb 14, 2008how to pass array of values in stored procedure..
View 2 Replies View RelatedStored Procedure With Array Of Parameters
Nov 23, 2005I have a table on the database with columns like the following:Name Date DataJoe 11/5/05 data1Joe 11/6/05 data2Bob 11/5/05 data3Bob 11/8/05 data4I want to retrieve all data from an array or list I pass in thatcontainsone row for each name and a date, like below.Name DateJoe 11/6/05Bob 11/7/05I want to retrieve all rows from the first table where Name is Joe andDate > 11/6/05 or where Name is Bob and Date is > 11/7/05. There couldbe an unlimited number of name/date combinations.Can anyone suggest a way to write a stored procedure to handle thisquery.Thanks,Rick
View 4 Replies View RelatedIs It Possible To Pass Array To Stored Procedure
Jan 9, 2007Dear All,
I am using sql2000, I want to know whether in stored procedure we can pass
array. Or is there any other solution to pass array of records
Please Guide Me
thanks
Passing An Array Of Strings To A Stored Procedure
Jun 10, 2007Well, I managed to write a Stored procedure that updates some records in the Northwind Database based on the ProductIDs passed to the SP as a list of strings. This is the Alter version of the SP:USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[gv_sp_UpdatePOs]
(
@IDList varchar(500),
@ReorderLevel int,
@ProductName nvarchar(30)
)
AS
BEGIN
SET NOCOUNT ON
EXEC('Update dbo.Products
SET ReorderLevel = (' + @ReorderLevel + ') ,ProductName = (''' + @ProductName + ''')
WHERE ProductID IN (' + @IDList + ')')
END
----------------------
THis works fine inside Sql Server 2005 Query analyser.
But when I setup an aspx page with an objectDataSource inside the page attached to an xsd file where the Products table is located. When I try to add new query to the tableadapter inside the Products table and point to the stored procedure in the wizard I get this error: " the wizard detected the following problems when configuring TableAdapter query "Products" Details: Generated SELECT statement. Incorrect suntax near ')'.
Any help would be appreciated
And can someone convert it to support XML instead of list of strings. thanks.
Can SQL Break An Array Into One String? [Stored Procedure]
Jun 14, 2006Hello, I have a question on sql stored procedures.I have such a procedure, which returnes me rows with ID-s.Then in my asp.net page I make from that Id-s a string likeSELECT * FROM [eai.Documents] WHERE CategoryId=11 OR CategoryId=16 OR CategoryId=18.
My question is: Can I do the same in my stored procedure? Here is it:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[eai.GetSubCategoriesById]
(
@Id int
)
AS
declare @path varchar(100);
SELECT @path=Path FROM [eai.FileCategories] WHERE Id = @Id;
SELECT Id, ParentCategoryId, Name, NumActiveAds FROM [eai.FileCategories]
WHERE Path LIKE @Path + '%'
ORDER BY Path
Thank youArtashes
For Loop List Of Array In Stored Procedure
Aug 19, 2014I would like to write a store prodecure to return a month:
My output:
Wk1 = July
Wk2 = July
Wk3 = July
Wk4 = July
Wk5 = Aug
and so on..
then i create list of array like below:
The counter for insert the week one by one
DECLARE @TotalWeek INT, @counter INT
DECLARE @WeekNo varchar, @Month varchar
SET @WeekNo = '4,9,14,18,22,27,31,35,40,44,48,53'
--this is weekno,if less than 4, month is july, lf less than 9, month is august and so on
SET @TotalWeek = 53
SET @counter = 1
[Code] ....
Working With An Integer Array In A Stored Procedure
May 4, 2006hi there,
i need a procedure that works with C# e.g.:
using (SqlCommand cmd = GetCommand("Procedure_Name"))
{
//i=an array of integer values
cmd.Parameters.Add("@array", SqlDbType.????!?!???).Value = i;
cmd.ExecuteScalar();
}
i need to write a stored procedure that takes as input an array of integers (amongst other values)
this procedure must loop through every integer in the array and INSERT a new record into a table.
i have never used T-SQL before.
Many thanks
Passing An Array Into A Stored Procedure And The 'IN' Clause Will Not Do The Trick
Mar 6, 2008I have a table that looks like this:
RecordId
PictureId
KeywordId
111
212
313
421
522
623
725
817
932
1044
I need to run a query where I pass in an unknown number of KeywordIds that returns the PictureId. The 'IN' clause will not work because if a KeyWordId gets passed into the Stored Procudure the PictureId must have a record with each KeyWordId being passed in. For example, lets say you need to see the result of all PictureIds that have both 1 and 2, the correct result set should only be PictureId 1 and PictureId 2.
Im going crazy trying to find a simple solution for this. Please advise.
Passing A List/array To An SQL Server Stored Procedure 2005
Aug 16, 2007Hi, I m using sql 2005 as a back end in my application...
I am useing Store procedure..for my data in grid..
ALTER PROCEDURE [dbo].[ProductZoneSearct]
(
@Productid char(8),@Proname char(8),@radius int,@mode varchar(5) = 'M',@Zone nvarchar(1000),)
ASSET NOCOUNT ON;Create Table #Product (ProductID int, TimeEntered datetime, DateAvailable datetime, Productname varchar(80), City varchar(50), State char(4),Miles decimal, Payment varchar(40),UserID int, Phone varchar(15))
Insert #Product Select ProductID , TimeEntered, DateAvailable, Productname ,City,State,miles,Payment ,Miles, UserID, Daily, PhoneFrom [tblproduct] Where city IN (@Zone)
Select ProductID TimeEntered, DateAvailable, Productname City,State,miles,Payment ,Miles, U.Phone As phoneNumber, Company, , L.Phone As cmpPhone From #Product As L Left Join (Select UserID, Company, Phone, From [User]) As U On U.UserID = L.UserID Order By DateAvailable
if i pass value in "where city in (@Zone)" and @Zone ='CA','AD','MH' then it can not get any result..but if write where city in ('CA','AD','MH') then it give me perfact result..
I tried to below syntax also but in no any user Where city IN ('+@Zone+')
In short if i pass value through varibale (@Zone) then i cant get result...but if i put direct value in query then only getting result..can anybody tell me what is problem ?
Please Hel[p me !!!
Thank you !!!
How Do I Send Someone A Stored Procedure?
Jan 18, 2001I have created some stored procedures that I wish to share with another user in another database. How can I extract this code, other than cut and paste, since I have quite a few? Is it possible to duplicate database object "stored procedures"? How about a script that would recreate them in the target database?
Any help would be appreciated.
Thanks!
Best Way To Send Email From A C# Stored Procedure?
Jul 2, 2006I thought I could just copy over some asp.net code like: System.Web.Mail.MailMessage mailMessage = new System.Web.Mail.MailMessage();
But VS2005 doesn't seem to want me touching System.Web.Mail.
Any ideas?
Thanks,
Allen
Stored Procedure Has To Send An Email !!!!
Mar 17, 2004Hi,
I am trying to write a stored procedure in Sql Server that should send an email containing a query result everyday at 2:00 PM. How can I do this?? Im trying to use xp_sendmail but this gives me the following error:
Could not find stored procedure 'xp_sendmail'.
Plz let me know if there is any easy way to handle this.
Thanks a lot
How To Send Email From Stored Procedure
Jan 30, 2014I have an store procedure and I want to build an email with this store procedure to email me How can I use the email command to incorporate into my sql SP?
View 4 Replies View RelatedSend Null Values To A Stored Procedure In C#
Jul 11, 2007Hi
I am new to C# . I have a stored procedure which takes 4 parameters
GetSearchComplaint( Comp_ID , strViolator, strSts, FromDate, ToDate), These parameters can be null.
And i have 5 textboxes from which i send the parameters.
I am validating the input like this :System.Nullable<int> Comp_ID;
if ((txtsrchCompID.Text).Trim() == "")
{Comp_ID = null;
}else if ((txtsrchCompID.Text).Trim() == "")
{
try
{int i = int.Parse(txtsrchCompID.Text);
Comp_ID =i;
catch
{mesage += "Complaint ID is not valid";
}
}
When i run this i get this error ---'Use of unassigned local variable 'Comp_ID'
I get the same error for FromDate(DateTime) and ToDate(DateTime) . but not for string variables strViolator and strSts.
How do i pass the null value to the stored procedure? pls help..
Using Stored Procedure Send Web Request Failed
Mar 20, 2007I am tring to use a stored procedure to run a function POST some data through HTTP, but failed. Here are the codes I used. Would appreciate if anyone can help me about that. Thank you very much.
Database Stored Procedure:
CREATE PROCEDURE [dbo].[USP_HTTP_POST]
@URL NCHAR(255),
@CONTENT NCHAR(1024),
@Result bit OUTPUT
AS
BEGIN
/***** Call Function to POST *****/
set @Result = NULL
EXECUTE @Result = [dbo].[udfHttpPost]
@URL,
@Content
END
Database Function:
CREATE FUNCTION [dbo].[udfHttpPost]
(@Url [nchar](255),
@Content [nchar](1024))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServices].[SqlServices.HttpServices].[HttpPost]
VC# Assembly Code
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Security;
using System.Security.Permissions;
using System.IO;
using Microsoft.SqlServer.Server;
namespace SqlServices
{
public class HttpServices
{
[SqlFunction(DataAccess = DataAccessKind.None)]
public static bool HttpPost(string Url, string Content)
{
Url = Url.TrimEnd();
Content = Content.TrimEnd();
string rawOutput = "";
//Get Access Right to Web
WebPermission p = new WebPermission(NetworkAccess.Connect,Url);
p.Assert();
//Prepare the WebRequest
WebRequest Req = (WebRequest)WebRequest.Create(Url);
Req.Timeout = 60000;
Req.Method = "POST";
Req.ContentLength = Content.Length;
Req.ContentType = "application/x-www-form-urlencoded";
StreamWriter PostWriter = new StreamWriter(Req.GetRequestStream());
PostWriter.Write(Content);
PostWriter.Close();
WebResponse Resp = Req.GetResponse();
StreamReader sr = new StreamReader(Resp.GetResponseStream());
rawOutput = sr.ReadToEnd();
sr.Close();
return true;
}
}
}
System Exception:
A .NET Framework error occurred during execution of user defined routine or aggregate 'udfHttpPost':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 unrestrictedOverride, Int32 create)
at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Assert()
at SqlServices.HttpServices.HttpPost(String Url, String Content)
How To Send Sql/stored Procedure Output In The Body Of The Email.
Nov 3, 2007Everyday morning I email the sql query/stored procedure output results to the users, I was wondering if I can use some kind of t-sql code or DTS packages so that I can automate this process where I want to send the sql/stored proc results in the body of the email.
View 7 Replies View RelatedSQL Server 2000 - Send A Mail By Stored Procedure
Mar 3, 2008can I send a mail by stored procedure?
View 5 Replies View RelatedCreating Stored Procedure To Send Email To Multiple Users
Sep 20, 2007Hi Everybody,
I am trying to setup a stored procedure that runs through a Reminders table and sends an email to users based on DateSent field being smaller than todays date. I have already setup the stored procedure to send the email, just having trouble looping through the recordset.
Code Snippet
CREATE PROCEDURE [dbo].[hrDB_SendEmail]
AS
BEGIN
DECLARE @FirstName nvarchar(256),
@LastName nvarchar(256),
@To nvarchar(256),
@ToMgr nvarchar(256),
@Subject nvarchar(256),
@Msg nvarchar(256),
@DateToSend datetime,
@Sent nvarchar(256),
@ReminderID int,
@RowCount int,
@Today datetime,
@Result nvarchar(256)
-- Get the reminders to send
SELECT
@ReminderID = r.intReminderID,
@DateToSend = r.datDateToSend,
@FirstName = e.txtFirstName,
@LastName = e.txtLastName,
@To = e.txtEmail,
@Subject = t.txtReminderSubject,
@Sent = r.txtSent
FROM
(auto_reminders r INNER JOIN employee e ON r.intEmployeeID = e.intEmployeeID) INNER JOIN ref_reminders t ON r.intReminderType = t.intReminderTempID
WHERE
(((r.datDateToSend)<20/12/09) AND
((r.txtSent)='False'))
-- Send the Emails
WHILE(LEN(@To) > 0)
BEGIN
EXEC @Result = sp_send_cdosysmail @To, @ToMgr, @Subject, @Msg
END
-- Mark the records as sent
IF @Result = 'sp_OAGetErrorInfo'
BEGIN
SELECT @Sent = 'Error'
END
ELSE
BEGIN
SELECT @Sent = 'True'
END
UPDATE auto_reminders
SET
auto_reminders.txtSent = @Sent, auto_reminders.datDateSent = @Today
WHERE
intReminderID = @ReminderID
END
GO
From the code you can probably tell I am new to writing stored procedures, so I apologise for any obvious errors. My major problems are :-
how to loop through each record
how to get todays date
whether the struture of the procedure is correct
Also, if you think there is an easier way or a better method, please suggest it. I am open to any suggestions you may have,
Thanks in advance
Ben
Return An Unique Identifier To An ASP.NET Page To Send It As A Parameter Into Another Stored Procedure
May 1, 2007Hi !
I have a problem with the unique identifier and don't know how to solve it.
I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values.
I tried with SCOPE_IDENTITY but i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable.
Dim cmd As New SqlCommand
cmd.CommandText = "Insertar_Contacto"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
Thanks!!
Best Way To Send Email From A Stored Procedure (dynamically Changing Paramenters And Attachment)
Jul 23, 2005Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
View 2 Replies View RelatedHelp With How To Send E-Mail (Parent - Child Matching Records) From SQL 2005 Stored Procedure.
Oct 31, 2007Folks,Using NorthWind as Example: Parent Table derived from: Categories. I added a new Column E-Mail and Selecting rows where Category Id <=3. Here is my Data.
Category ID
Category Name
Category E-mail
1
Beverages
Beverages.com
2
Condiments
Condiments.com
3
Confections
Child Table derived from: Products. I am Selecting rows where Category Id <=3. Here is my Sample Data.
Category ID
Product Name
Quantity Per Unit
1
Chang
24 - 12 oz bottles
1
Côte de Blaye
12 - 75 cl bottles
1
Ipoh Coffee
16 - 500 g tins
1
Outback Lager
24 - 355 ml bottles
2
Aniseed Syrup
12 - 550 ml bottles
2
Chef Anton's Gumbo Mix
36 boxes
2
Louisiana Hot Spiced Okra
24 - 8 oz jars
2
Northwoods Cranberry Sauce
12 - 12 oz jars
3
Chocolade
10 pkgs.
3
Gumbär Gummibärchen
100 - 250 g bags
3
Maxilaku
24 - 50 g pkgs.
3
Scottish Longbreads
10 boxes x 8 pieces
3
Sir Rodney's Scones
24 pkgs. x 4 pieces
3
Tarte au sucre
48 piesI would like to read 1st Category Id, Category E-Mail from Categories Table (ie. Category Id = 1), find that in Products Table. If match, extract matching records for that Category from Both Tables (Categories.CategoryID, Products.ProductName, Products.QuantityPerUnit) and e-mail them based on E-Mail Address from Parent (Categories ) Table. If no E-Mail Address is listed, do not create output file. In this instance Category Id = 3.Basically I want to select 1st record from Parent Table (Here is Category) and search for all matching Products in Products Table. And Create an E-mail and sending just those matching records. Repeat the same process for remaining rows from Categories Table. I am expecting my E-Mail Output like this: For Category Id: 1
Category ID
Product Name
Quantity Per Unit
1
Chang
24 - 12 oz bottles
1
Côte de Blaye
12 - 75 cl bottles
1
Ipoh Coffee
16 - 500 g tins
1
Outback Lager
24 - 355 ml bottlesFor Category Id: 2
Category ID
Product Name
Quantity Per Unit
2
Aniseed Syrup
12 - 550 ml bottles
2
Chef Anton's Gumbo Mix
36 boxes
2
Louisiana Hot Spiced Okra
24 - 8 oz jars
2
Northwoods Cranberry Sauce
12 - 12 oz jarsI am not extracting the Data for any user Interface (ie. Grid View/Form View Etc). I will just create a Command Button in an ASP.NET 2.0 form to extract Data. My Tables are in SQL 2005. I was thinking to read the Category records in a Data Reader and within the While Loop, call a SP to retrieve the matching records from Products Table. If matching records found, call System SP_Mail to send the E-mail. The drawback with that for every category records (Within While Loop) I need to call my SP to get Products Data. Will be OVERKILL? Ideally I would like extract my records with one call to a SP. Is there any way I can run a while loop inside the SP and extract Child Data based on Parent Record? Any Help or sample URL, Tutorial Page will be appreciated. Thanks
Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field
Sep 20, 2007I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
Send Request To Stored Procedure From A Method And Receive The Resposne Back To The Method
May 10, 2007Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies View RelatedHelp Send An Personal Email From Database Mail On Row Update-stored PROCEDURE Multi Update
May 27, 2008hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
Array As Parameters To Procedure
Jun 1, 2005Hi friends,Please help me I want to pass an Aarry parameters from asp.net to sqlserver stored procedure. Is it possible, if yes how.regards,Asad Mahmood
View 2 Replies View Related