Send GUID ARRay To The Stored Procedure

Apr 28, 2008

Hi 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

View 2 Replies


ADVERTISEMENT

How Would I Send A String Array As A Integer Array?

Jun 25, 2007

I 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

View 5 Replies View Related

How To Pass A GUID String To A Varchar In SQL Stored Procedure

Aug 26, 2007

In 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?

View 1 Replies View Related

Array In A Stored Procedure

Jun 1, 2006

Can 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.

View 5 Replies View Related

Passing Array With Ids To Stored Procedure

Apr 1, 2004

I 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.

View 2 Replies View Related

How To Impliment Array In Stored Procedure?

Apr 8, 2005

i 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?

View 1 Replies View Related

How Array Will Pass To Stored Procedure

May 7, 2001

I 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 Related

How To Create An Array In A Stored Procedure

Aug 8, 2000

I 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 Related

Array Of Values Using Stored Procedure

Feb 14, 2008

how to pass array of values in stored procedure..

View 2 Replies View Related

Stored Procedure With Array Of Parameters

Nov 23, 2005

I 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 Related

Is It Possible To Pass Array To Stored Procedure

Jan 9, 2007

Dear 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

View 3 Replies View Related

Passing An Array Of Strings To A Stored Procedure

Jun 10, 2007

Well, 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.
 

View 3 Replies View Related

Can SQL Break An Array Into One String? [Stored Procedure]

Jun 14, 2006

Hello, 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

View 2 Replies View Related

For Loop List Of Array In Stored Procedure

Aug 19, 2014

I 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] ....

View 8 Replies View Related

Working With An Integer Array In A Stored Procedure

May 4, 2006

hi 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

View 3 Replies View Related

Passing An Array Into A Stored Procedure And The 'IN' Clause Will Not Do The Trick

Mar 6, 2008

I 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.

View 7 Replies View Related

Passing A List/array To An SQL Server Stored Procedure 2005

Aug 16, 2007

Hi, 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 !!!

View 5 Replies View Related

How Do I Send Someone A Stored Procedure?

Jan 18, 2001

I 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!

View 2 Replies View Related

Best Way To Send Email From A C# Stored Procedure?

Jul 2, 2006

I 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

View 6 Replies View Related

Stored Procedure Has To Send An Email !!!!

Mar 17, 2004

Hi,


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

View 3 Replies View Related

How To Send Email From Stored Procedure

Jan 30, 2014

I 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 Related

Send Null Values To A Stored Procedure In C#

Jul 11, 2007

Hi
 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..

View 7 Replies View Related

Using Stored Procedure Send Web Request Failed

Mar 20, 2007

I 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)

 

View 6 Replies View Related

How To Send Sql/stored Procedure Output In The Body Of The Email.

Nov 3, 2007

Everyday 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 Related

SQL Server 2000 - Send A Mail By Stored Procedure

Mar 3, 2008

can I send a mail by stored procedure?

View 5 Replies View Related

Creating Stored Procedure To Send Email To Multiple Users

Sep 20, 2007

Hi 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

View 1 Replies View Related

Return An Unique Identifier To An ASP.NET Page To Send It As A Parameter Into Another Stored Procedure

May 1, 2007

Hi !
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!!
 

View 2 Replies View Related

Best Way To Send Email From A Stored Procedure (dynamically Changing Paramenters And Attachment)

Jul 23, 2005

Hello 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 Related

Help With How To Send E-Mail (Parent - Child Matching Records) From SQL 2005 Stored Procedure.

Oct 31, 2007

Folks,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
 

View 3 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I 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

View 6 Replies View Related

Send Request To Stored Procedure From A Method And Receive The Resposne Back To The Method

May 10, 2007

Hi,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 Related

Help Send An Personal Email From Database Mail On Row Update-stored PROCEDURE Multi Update

May 27, 2008

hi 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

View 2 Replies View Related

Array As Parameters To Procedure

Jun 1, 2005

Hi 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







Copyrights 2005-15 www.BigResource.com, All rights reserved