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


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

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

Can I Add Integer Array To SQLCommand As Parameter?

Jun 3, 2008

I'm trying to build an SQL string that should look like this when executed:
UPDATE [Table] SET Active = 'False' WHERE ID IN (3, 4, 5, 6, 7, etc.)
I'm using the convention (in code behind):SqlCommand cmd = new SqlCommand("UPDATE [Table] SET Active = 'False' WHERE ID IN (@TheIDs)", connection);cmd.Parameters.Add("TheIDs", SqlDbType.Text).Value = theIDsAsAnArrayList;
But logically enough I cannot insert them as a text string as they have to be integers seperated by commas.
Question: How can I convert an Array of integers into ... well, a string without the quotes, if you know what I mean?
As it cannot end up like this: UPDATE [Table] SET Active = 'False' WHERE ID IN ("3, 4, 5, 6, 7, etc.")
Note the quotes around the integers.
Any hints on doing this with security in mind are welcome. I know I can concatenate the whole lot as strings, but this is unsecure, so I'm not going for that approach.

View 4 Replies View Related

Could A Datatype Of A Column Be Like Integer Array?

Aug 7, 2004

Hi everyone....
I need a column in my table that its DataType should be as integer arrays how can I implemant it in my sqlserver table.
Any help appreciated.

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

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 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

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

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 To Execute A Dynamic SQL With Integer Parameter For Stored Procedure?

Sep 17, 2007

I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck.
Error:  Unterminated String Constant.
What is the problem?
Set @strSQL='Select * From(SELECT Row_Number() Over(Order By ' + @SortExpression + ') as Row_Count,Rank() Over (Order By ' + @SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID, dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAMEFROM dbo.EVENT_LOGS INNER JOINdbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID) as TableInfoWhere Row_Count Between ' + @startRowIndex + ' and ' + @maxRowIndex + ' ';Exec(@strSQL);

View 3 Replies View Related

Pass Multivalue Parameter To A Stored Procedure For Integer Lookup

Feb 19, 2007

I'd like to pass a multi-value parameter to a stored proc to satisfy an integer field lookup.

example -

CREATE PROC SSRSQuery

@InPublicationId VARCHAR(500) = NULL AS

SELECT * from Table where PublicationId IN (@InPublicationId)

where PublicationId is defined as an int

I've seen various posts on how to split up the input string parameter to use in a string-based lookup but nothing useful for an integer-based lookup.

Any tips or tricks for this?

View 3 Replies View Related

Transact SQL :: INSERT Array Of Integer Into Table - Wrong Return Value Of Statement In ODBC

Sep 30, 2015

I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:

#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include<tchar.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>

[Code] ....

In my code, I would like to Insert the array and then count the number of rows:

SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;

Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of  existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.

Hint : If I monitor the database using SQL Profiler. It looks like this:

Why this statement doesn't work correctly?

View 7 Replies View Related

Help Stored Procedure Working But Not Doing Anything

Jul 31, 2006

Help Stored procedure working but not doing anything New Post
Quote    Reply
Please i need some help.

I am calling a stored procedure from  asp.net and there is a
cursor in the stored procedure that does some processing on serval
tables.

if i run the stored procedure on Query Analyzer it works and does what
it is suppose to do but if i run it from my asp.net/module control it
goes. acts likes it worked but it does not do what is suppose to do.
i believe the cursor in the stroed procedure does not run where is
called programmatically from the asp.net/module control page.plus it
does not throw any errors


This is the code from my control
System.Data.SqlClient.SqlParameter [] param={new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(startDate)),new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(endDate)),new
System.Data.SqlClient.SqlParameter("@addedby",UserInfo.FullName+ "
"+UserInfo.Username)};
               
string
str=System.Configuration.ConfigurationSettings.AppSettings["payrollDS"];
               
System.Data.SqlClient.SqlConnection cn=new
System.Data.SqlClient.SqlConnection(str);
               
cn.Open();              

               
//System.Data.SqlClient.SqlTransaction trans=cn.BeginTransaction();

               
SqlHelper.ExecuteScalar(cn,System.Data.CommandType.StoredProcedure,"generatePaylistTuned",param);
      


------------------------THis is the code for my storedprocedure-------------

CREATE PROCEDURE [dbo].[generatePaylistTuned]
@periodStart datetime,
@periodEnd datetime,
@addedby varchar(40)

AS

begin transaction generatePayList

DECLARE @pensioner_id int, @dateadded datetime,
 @amountpaid float,
@currentMonthlypension float,@actionType varchar(50),
@isAlive bit,@isActive bit,@message varchar(80),@NoOfLoadedPensioners int,
@NoOfDeadPensioners int,@NoOfEnrolledPensioners int,@DeactivatedPensioners int,
@reportSummary varchar(500)

set @NoOfLoadedPensioners =0

set @NoOfDeadPensioners=0
set @NoOfEnrolledPensioners=0
set @DeactivatedPensioners=0
set @actionType ="PayList Generation"

DECLARE paylist_cursor CURSOR FORWARD_ONLY READ_ONLY FOR

select p.pensionerId,p.isAlive,p.isActive,py.currentMonthlypension
from pensioner p left outer join pensionpaypoint py on  p.pensionerid=py.pensionerId

where p.isActive = 1


OPEN paylist_cursor

FETCH NEXT FROM paylist_cursor
INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension

WHILE @@FETCH_STATUS = 0
BEGIN

set @NoOfLoadedPensioners=@NoOfLoadedPensioners+1
if(@isAlive=0)
begin
update Pensioner
set isActive=0
where pensionerid=@pensioner_id
set @DeactivatedPensioners =@@ROWCOUNT+@DeactivatedPensioners
set @NoOfDeadPensioners =@@ROWCOUNT+@NoOfDeadPensioners
end
else
begin
insert into pensionpaylist(pensionerId,dateAdded,addedBy,
periodStart,periodEnd,amountPaid)
values(@pensioner_id,getDate(),@addedby, @periodStart, @periodEnd,@currentMonthlypension)
set @NoOfEnrolledPensioners =@@ROWCOUNT+ @NoOfEnrolledPensioners
end

   -- Get the next author.
   FETCH NEXT FROM paylist_cursor
   INTO  @pensioner_id,@isAlive,@isActive,@currentMonthlypension
END

CLOSE paylist_cursor
DEALLOCATE paylist_cursor

set @reportSummary ="The No. of Pensioners Loaded:
"+Convert(varchar,@NoOfLoadedPensioners)+"<BR>"+"The No. Of
Deactivated Pensioners:
"+Convert(varchar,@DeactivatedPensioners)+"<BR>"+"The No. of
Enrolled Pensioners:
"+Convert(varchar,@NoOfEnrolledPensioners)+"<BR>"+"No Of Dead
Pensioner from Pensioners Loaded: "+Convert(varchar,@NoOfDeadPensioners)
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),0, @periodStart, @periodEnd,@reportSummary,'Pay List Generation')

 if (@@ERROR <> 0)               
          BEGIN

insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),1, @periodStart,@periodEnd,@reportSummary,'Pay List Generation')

ROLLBACK TRANSACTION  generatePayList

          END

commit Transaction generatePayList
GO

View 5 Replies View Related

Set Identity Not Working For Stored Procedure

Jan 31, 2008

Hi can someone tell me whats wrong with this stored procedure. All im trying to do is get the publicationID from the publication table in order to use it for an insert statement into another table. The second table PublicaitonFile has the publicaitonID as a foriegn key.
Stored procedure error: cannot insert null into column publicationID, table PublicationFile - its obviously not getting the ID.
ALTER PROCEDURE dbo.StoredProcedureUpdateDocLocField
@publicationID Int=null,@title nvarchar(MAX)=null,@filePath nvarchar(MAX)=null
ASBEGINSET NOCOUNT ON
IF EXISTS (SELECT * FROM Publication WHERE title = @title)SELECT @publicationID = (SELECT publicationID FROM Publication WHERE title = @title)SET @publicationID = @@IDENTITYEND
IF NOT EXISTS(SELECT * FROM PublicationFiles WHERE publicationID = @publicationID)BEGININSERT INTO PublicationFile (publicationID, filePath)VALUES (@publicationID, @filePath)END
 

View 5 Replies View Related

Stored Procedure - Insert Not Working

Jun 23, 2005

Having a little trouble not seeing why this insert is not happening.... --snip--  DECLARE c_studId CURSOR FOR  SELECT studentId  FROM students FOR READ ONLY   OPEN c_studId  FETCH NEXT FROM c_studId INTO @studentId  IF( @@FETCH_STATUS = 0 )  BEGIN   SET @studRec = 'Found'  END CLOSE c_studId DEALLOCATE c_studId
 BEGIN TRAN IF (@studRec <> 'Found')  BEGIN  INSERT INTO students  (studentId)  VALUES  (@studentId)    END  Well, you get the idea, and I snipped a lot of it.Why is it not inserting if the user is not found?Thanks all,Zath

View 6 Replies View Related

Simple Stored Procedure Not Working

Jul 14, 2005

I have a SP below that authenticates users, the problem I have is that activate is of type BIT and I can set it to 1 or 0.
If I set it to 0 which is disabled, the user can still login.
Therefore I want users that have activate as 1 to be able to login and users with activate as 0 not to login

 what are mine doing wrong ?

Please help


CREATE PROCEDURE DBAuthenticate

(
  @username Varchar( 100 ),
  @password Varchar( 100 )
)
As

DECLARE @ID INT
DECLARE @actualPassword Varchar( 100 )

SELECT
  @ID = IdentityCol,
  @actualPassword = password
  FROM CandidatesAccount
  WHERE username = @username and Activate = 1

IF @ID IS NOT NULL
  IF @password = @actualPassword
    RETURN @ID
  ELSE
    RETURN - 2
ELSE
  RETURN - 1
GO

View 5 Replies View Related

Stored Procedure Stops Working

Jun 1, 1999

I have a stored procedure which does a simple select joining 3 tables.

This had been working fine for weeks and then just stopped returning any rows even though data existed for it to return.

After re-compiling the procedure, it worked fine as before.

Does anyone know of any reason why a procedure would need recompiling like this?

We have been doing data restores and also dropping/recreating tables during this development. Would any of this affect a procedure?

View 3 Replies View Related

Why Isnt My Stored Procedure Working

Oct 24, 2007

Hi there below is my code for a sproc. however when i run it, it doesnt seem to create a table











USE [dw_data]
GO
/****** Object: StoredProcedure [dbo].[usp_address] Script Date: 10/24/2007 15:33:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[usp_address]
(
@TableType INT = null

)

AS

IF @TableType is NULL OR @TableType = 1

BEGIN

IF NOT EXISTS (SELECT 1 FROM [DW_BUILD].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'address')

CREATE TABLE [dw_build].[dbo].[address] (
[_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[county] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[end_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[forename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[other_inv_link] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[out_of_district] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[packed_address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paf_ignore] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paf_valid] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[patient] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[patient_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pct_of_res] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[postcode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[real_end_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[relationship] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[relationship_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[surname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[telephone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[town] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updated] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

END

IF @TableType is NULL OR @TableType = 2

BEGIN

CREATE TABLE [dw_build].[dbo].[address_cl] (
[_id] [int] NULL,
[address_1] [varchar](40) NULL,
[address_2] [varchar](40) NULL,
[category] [varchar](7) NULL,
[category_userno] [int] NULL,
[county] [varchar](40) NULL,
[created] [datetime] NULL,
[creator] [int] NULL,
[end_date] [datetime] NULL,
[forename] [varchar](40) NULL,
[notes] [varchar](max) NULL,
[other_inv_link] [int] NULL,
[out_of_district] [bit] NOT NULL,
[packed_address] [varchar](220) NULL,
[paf_ignore] [bit] NOT NULL,
[paf_valid] [bit] NOT NULL,
[patient] [int] NULL,
[patient_date] [datetime] NULL,
[pct_of_res] [int] NULL,
[postcode] [varchar](40) NULL,
[real_end_date] [datetime] NULL,
[relationship] [varchar](7) NULL,
[relationship_userno] [int] NULL,
[surname] [varchar](40) NULL,
[telephone] [varchar](40) NULL,
[title] [varchar](40) NULL,
[town] [varchar](40) NULL,
[updated] [datetime] NULL,
[updator] [int] NULL
) ON [PRIMARY]

END

View 13 Replies View Related

Stored Procedure Range Not Working

Dec 22, 2006

Hi All,I am trying to write a basic stored procedure to return a range ofvalues but admit that I am stumped. The procedure syntax used is:ALTER PROCEDURE Rpt_LegacyPurchaseOrderSp(@StartPoNumber PONumberType = 'Null',@FinishPoNumber PONumberType = 'Null')ASSET @StartPoNumber = 'PO_NUMBER_POMSTR'SET @FinishPoNumber = 'PO_NUMBER_POMSTR'SELECTIPPOMST_SID,--Start tbl_IPPOMSTPO_NUMBER_POMSTR,VENDOR_NUMBER_POMSTR,SHIP_NUMBER_POMSTR,CHANGE_ORDER_POMSTR,FOB_POINT_POMSTR,ROUTING_POMSTR,DATE_ISSUED_POMSTR,DATE_LAST_RECPT_POMSTR,CONTACT_PERSON_1_POMSTR,PREPAID_COLLECT_POMSTR,TERMS_POMSTR,AMOUNT_ESTIMATED_POMSTR,AMOUNT_RECEIVED_POMSTR,AMOUNT_PAID_POMSTR,LOCATION_CODE_POMSTR,SHIPPING_POINT_POMSTR,PRINT_IND_POMSTR,BUYER_POMSTR,SHIPMENT_POMSTR,STATUS_POMSTR,CURRENCY_POMSTR,CURRENCY_STATUS_POMSTR,AMOUNT_EST_CUR_POMSTR,AMOUNT_REC_CUR_POMSTR,AMOUNT_PAID_CUR_POMSTR,--Finish tbl_IPPOMSTIPPOITM_SID,--Start tbl_IPPOITMPO_NUMBER_POITEM,ITEM_NUMBER_POITEM,CATEGORY_POITEM,DESCRIPTION_POITEM,VENDOR_NUMBER_POITEM,DATE_ORIGINAL,DATE_RESCHEDULED,ACCOUNT_NUMBER_POITEM,STOCK_NUMBER_POITEM,JOB_NUMBER_POITEM,RELEASE_WO_POITEM,QUANTITY_ORDERED_POITEM,QUANTITY_RECVD_POITEM,UOM_POITEM,UNIT_WEIGHT_POITEM,UNIT_COST_POITEM,EXTENDED_TOTAL_POITEM,MATERIAL_NUMBER_POITEM,COMPLETE_POITEM,LOCATION_CODE_POITEM,INSPECTION_POITEM,BOM_ITEM_POITEM,COST_ACCOUNT_POITEM,CHANGE_ORDER_POITEM,TAX_CODE_POITEM,ISSUE_CODE_POITEM,QUANTITY_INSPECT_POITEM,EXC_RATE_CURR_POITEM,UNIT_COST_CURR_POITEM,EXTENDED_TOTAL_CURR_POITEM,PLANNER_POITEM,BUYER_POITEM--Finish tbl_IPPOITMIPVENDM_SID,--Start tbl_IPVENDMVENDOR_NUMBER_VENMSTR,VENDOR_NAME_VENMSTR,ADDRESS_LINE_1_VENMSTR,ADDRESS_LINE_2_VENMSTR,ADDRESS_LINE_3_VENMSTR,CITY_VENMSTR,STATE_VENMSTR,ZIP_CODE_VENMSTR,COUNTRY_VENMSTR--Finish tbl_IPVENDMFROM tbl_IPPOMSTJOIN tbl_IPPOITMON tbl_IPPOITM.PO_NUMBER_POITEM = tbl_IPPOMST.PO_NUMBER_POMSTRJOIN tbl_IPVENDMon tbl_IPVENDM.VENDOR_NUMBER_VENMSTR = tbl_IPPOMST.VENDOR_NUMBER_POMSTRWHERE tbl_IPPOMST.PO_NUMBER_POMSTR >= @StartPoNumber ANDtbl_IPPOMST.PO_NUMBER_POMSTR <= @FinishPoNumberBasically, no rows are returned for the valid (records in database)range I enter. I have been troubleshopoting the syntax. This hasinvolved commenting out references to @FinishPoNumber so in effect Ijust pass in a valid PO Number using @StartPoNumber parameter. Thisworks in terms of returning all 76545 PO records.Can anyone help me to identify why this syntax will not return a rangeof PO records that fall between @StartPoNumber and @FinishPoNumber?Any help would be greatly appreciated.Many Thanks*rohan* & Merry Christmas!

View 2 Replies View Related

Update Stored Procedure Not Working When Called From C#

Jul 11, 2007

OK, I have been raking my brains with this and no solution yet. I simply want to update a field in a table given the record Id. When I try the SQL in standalone (no sp) it works and the field gets updated. When I do it by executing the stored procedure from a query window in the Express 2005 manager it works well too. When I use the stored procedure from C# then it does not work:
 1. ExecuteNonQuery() always returns -1 2. When retrieving the @RETURN_VALUE parameter I get -2, meaning that the SP did not find a matching record.
So, with #1 there is definitely something wrong as I would expect ExecuteNonQuery to return something meaningful and with #2 definitely strange as I am able to execute the same SQL code with those parameters from the manager and get the expected results.
Here is my code (some parts left out for brevity):1 int result = 0;
2 if (!String.IsNullOrEmpty(icaoCode))
3 {
4 icaoCode = icaoCode.Trim().ToUpper();
5 try
6 {
7 SqlCommand cmd = new SqlCommand(storedProcedureName);(StoredProcedure.ChangeAirportName);
8 cmd.Parameters.Add("@Icao", SqlDbType.Char, 4).Value = newName;
9 cmd.Parameters.Add("@AirportName", SqlDbType.NVarChar, 50).Value = (String.IsNullOrEmpty(newName) ? null : newName);
10 cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
11 cmd.Connection = mConnection; // connection has been opened already, not shown here
12 cmd.CommandType = CommandType.StoredProcedure;
13 int retval = cmd.ExecuteNonQuery(); // returns -1 somehow even when RETURN n is != -1
14 result = (int)cmd.Parameters["@RETURN_VALUE"].Value;
15
16 }
17 catch (Exception ex)
18 {
19 result = -1;
20 }
21 }

 And this is the stored procedure invoked by the code above:1 ALTER PROCEDURE [dbo].[ChangeAirfieldName]
2 -- Add the parameters for the stored procedure here
3 @Id bigint = null,-- Airport Id, OR
4 @Icao char(4) = null,-- ICAO code
5 @AirportName nvarchar(50)
6 AS
7 BEGIN
8 -- SET NOCOUNT ON added to prevent extra result sets from
9 -- interfering with SELECT statements.
10 SET NOCOUNT ON;
11
12 -- Parameter checking
13 IF @Id IS NULL AND @Icao IS NULL
14 BEGIN
15 RETURN -1;-- Did not specify which record to change
16 END
17 -- Get Id if not known given the ICAO code
18 IF @Id IS NULL
19 BEGIN
20 SET @Id = (SELECT [Id] FROM [dbo].[Airports] WHERE [Icao] = @Icao);
21 --PRINT @id
22 IF @Id IS NULL
23 BEGIN
24 RETURN -2;-- No airport found with that ICAO Id
25 END
26 END
27 -- Update record
28 UPDATE [dbo].[Airfields] SET [Name] = @AirportName WHERE [Id] = @Id;
29 RETURN @@ROWCOUNT
30 END

 As I said when I execute standalone UPDATE works fine, but when approaching it via C# it returns -2 (did not find Id).

View 2 Replies View Related

Executing A Job From A Stored Procedure Stopped Working

Apr 29, 2008



I've got a stored procedure in database A that calls the sp_start_job stored procedure in msdb as follows:

CREATE PROCEDURE xxxxx
WITH EXECUTE AS 'domainusername'

AS

EXEC msdb.dbo.sp_start_job B'jobname' ;

RETURN

The domainusername is the in the database sysadmin role and the owner of the job. To make this work originally, I had to change the msdb database to be trusted.

This worked for the past several months.

Now it doesn't work (perhaps after a reboot but not sure). The error I get is "The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'

I looked to make sure that the account had grant execute rights and it does. I tried setting it via GRANT statement and it was granted successfully yet the error still occurs. I've tried changing accounts and anything else I can think of to no avail.

Any ideas how to troubleshoot this issue. I've tried all the tricks I can think of.

Thanks - SM

View 3 Replies View Related

Stored Procedure Where Clause Not Working Properly

May 24, 2008

I am having a problem with this stored procedure. I'm using SQL Server 2005 Developer's edition and if I execute the procedure in a query window, I get no errors. Also, when the script runs from a website call there are no errors. The problem is that it doesn't return the information that is in the database. It is supposed to return the orders from Washington state between such and such dates. The orders are there in the database, so I think the where clause must be wrong.

Thanks for the help.

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommerceLibOrdersGetWashingtonState]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[CommerceLibOrdersGetWashingtonState]

(@ShippingStateProvince VARCHAR(50),

@ShippingCountry VARCHAR(50),

@StartDate smalldatetime,

@EndDate smalldatetime)

AS

SELECT OrderID,


DateCreated,

DateShipped,

Comments,

Status,

CustomerID,

AuthCode,

Reference,
ShippingCounty,

ShippingStateProvince,

ShippingCountry,

ShippingID,

TaxID,

ShippingAmount,

TaxAmount

FROM Orders

WHERE (DateCreated BETWEEN @StartDate AND @EndDate)

AND (ShippingStateProvince = @ShippingStateProvince)

AND (ShippingCountry = @ShippingCountry)

ORDER BY DateCreated DESC'

END

View 4 Replies View Related

Error In Stored Procedure While Working With Temp. Table

May 31, 2007

Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as :
String or binary data would be truncated.The statement has been terminated.
The procedure i created is as :
ALTER PROCEDURE fetchpersondetails
AS
CREATE Table #tempperson (personID int,FirstName nvarchar(200),LastName nvarchar(250),title nvarchar(150),Profession nvarchar(200),StreetAddress nvarchar(300),
StateAddress nvarchar(200),CityAddress nvarchar(200),CountryAddress nvarchar(200),ZipAddress nvarchar(200),Telephone nvarchar(200),Mobile nvarchar(200),
Fax nvarchar(200),Email nvarchar(250),NotesPub ntext,Affiliation nvarchar(200),Category nvarchar(200))
 
Insert into #tempperson
SELECT dbo.tblperson.personID, ISNULL(dbo.tblperson.fName, N'') + ' ' + ISNULL(dbo.tblperson.mName, N'') AS FirstName, dbo.tblperson.lname AS LastName,
dbo.tblperson.honor AS Title, dbo.tblperson.title AS Profession, dbo.tblperson.street + ' ' + ISNULL(dbo.tblperson.suite, N'') AS StreetAddress,
dbo.tblperson.city AS cityaddress, dbo.tblperson.state AS stateaddress, dbo.tblperson.postalCode AS zipaddress,
dbo.tblperson.Phone1 + ',' + ISNULL(dbo.tblperson.Phone2, N'') + ',' + ISNULL(dbo.tblperson.Phone3, N'') AS Telephone,
dbo.tblperson.mobilePhone AS mobile, dbo.tblperson.officeFax + ',' + ISNULL(dbo.tblperson.altOfficeFax, N'') + ',' + ISNULL(dbo.tblperson.altOfficeFax2,
N'') AS Fax, ISNULL(dbo.tblperson.Email1, N'') + ',' + ISNULL(dbo.tblperson.Email2, N'') + ',' + ISNULL(dbo.tblperson.Email3, N'') AS Email,
dbo.tblperson.notes AS NotesPub, dbo.tblOrganizations.orgName AS Affiliation, dbo.tblOrganizations.orgCategory AS Category,
dbo.tblCountry.countryNameFull AS countryaddress
FROM dbo.tblperson INNER JOIN
dbo.tblOrganizations ON dbo.tblperson.orgID = dbo.tblOrganizations.orgID INNER JOIN
dbo.tblCountry ON dbo.tblperson.countryCode = dbo.tblCountry.ISOCode
 
please let me know the solurion of this error. 
 

View 2 Replies View Related







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