Stored procedure and c# .net - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored procedure and c# .net

Author

Topic

Yasa_amin
Starting Member

4 Posts

Posted - 2009-09-15 : 10:15:04

Hi all
I'm trying to learn how to write stored procedures. I'm trying to create a search filter in c# application, the sql part of it will be in stored procedure. here is the code

C# part
SqlCommand cmdTest = new SqlCommand("Test1", conn);
SqlDataReader readerTest;
cmdTest.CommandType = CommandType.StoredProcedure;
cmdTest.Parameters.Add("@itemName",SqlDbType.VarChar);
cmdTest.Parameters.Add("@itemDesc",SqlDbType.VarChar);
cmdTest.Parameters.Add("@itemColour",SqlDbType.VarChar);
cmdTest.Parameters.Add("@itemBrand",SqlDbType.VarChar);
cmdTest.Parameters.Add("@itemCat", SqlDbType.Int);

so what i want to do is pass the values entered from the c# application to the sql stored procedure.
I want my select statment only to query fields where the user has enterd a value and to ignore all the null once

SQL part
CREATE PROCEDURE [Test1]

-- Add the parameters for the stored procedure here
@itemName varchar (50),
@itemDesc varchar (50),
@itemColour varchar (50),
@itemBrand varchar (50),
@itemCat int
AS
SELECT * FROM ITEMS WHERE
IF @itemName IS NOT NULL
BEGIN
database1 = @itemName
END

IF @itemDesc IS NOT NULL
BEGIN
database2 = @itemDesc
END

IF @itemColour IS NOT NULL
BEGIN
database3 = @itemColour
END

IF @itemBrand IS NOT NULL
BEGIN
database4 = @itemBrand
END

IF @CatId IS NOT NULL
BEGIN
database5 = @CatId
END

GO
so what i want it to do is to search through all the fields if the user does enter values in all of the fields. If the user enter 2 values only then i want to sql to query only the entered fields..

i hope i'm making sense..
thanks in advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-15 : 10:41:22

Here is one way to accomplish that. Are your columns really named [database1], [database2], etc ???
Make your inputs default to NULL when nothing is supplied. Look at COALESCE in Books Online.


CREATE PROCEDURE [Test1]

-- Add the parameters for the stored procedure here
@itemName varchar (50) = null,
@itemDesc varchar (50) = null,
@itemColour varchar (50) = null,
@itemBrand varchar (50) = null,
@itemCat int = null
AS

--validate here for things like making sure at least search parameter was passed

SELECT <Use an explicit column list instead of *>

from [ITEMS]

where  datebase1 = coalesce(@itemname, database1)
and    database2 = coalesce(@itemDesc, database2)
and    database3 = coalesce(@itemColour, database3)
and    database4 = coalesce(@itemBrand, database4)
and    database5 = coalesce(@CatId, database5)

GO

Be One with the Optimizer
TG

tengtium
Starting Member

5 Posts

Posted - 2009-09-16 : 02:39:26

http://www.sommarskog.se/dyn-search-2005.html

Yasa_amin
Starting Member

4 Posts

Posted - 2009-09-16 : 12:24:24

Thanks guys ur reponse helped alot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources