How to pass parameter to only one 'SELECT' ?

Naresh Seth

There is a stored proc as below with two selects. And i have to pass only one parameter (i.e Name ) to one SELECT through SQLQuery. How could i achieve that?

Stored procedure :

"USE [CustomerDB]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[GetCustomers] @Name nvarchar(max), @ZipCode int

SELECT [Id], [Name],[ZipCode] FROM [dbo].[Customers] WHERE [Name] LIKE (@Name);

SELECT [Id], [Name],[ZipCode] FROM [dbo].[Customers] WHERE [ZipCode] = @ZipCode;

GO"

public List GetCustomersByName(string name, int zipcode)

{

List ilst = new List();

using (CustomerContext cx = new CustomerContext())

{

var result = cx.Database.SqlQuery("GetCustomers. @Name", new SqlParameter("@Name", name));

foreach (Customer c in result)

{

Customer cust = new Customer();

cust.Name = c.Name;

cust.Zipcode = c.Zipcode;

ilst.Add(cust);

Console.WriteLine(cust.Name, cust.Zipcode);

}

return ilst;

}

Please assist.

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bryian Tan8y ago
Accepted answer
As pointed out by other members, you might need to modify the code to add a switch, here is an example

Add a If statement in the SP
CREATE PROCEDURE [dbo].[GetCustomers]
    @Name nvarchar(max) = NULL,
    @ZipCode VARCHAR(50) = NULL
AS

IF (ISNULL(@Name,'') <> '')
    SELECT [Id], [Name],[ZipCode] FROM [dbo].[Customers] WHERE [Name] LIKE (@Name);
IF (ISNULL(@ZipCode,'') <> '')
    SELECT [Id], [Name],[ZipCode] FROM [dbo].[Customers] WHERE [ZipCode] = @ZipCode;
GO
Then in the code
//get by name
var result = cx.Database.SqlQuery(
                      "GetCustomers @Name, @ZipCode",
                      new SqlParameter("@Name", name),
                      new SqlParameter("@ZipCode", "")
                  )

//get by Zipcode
var result = cx.Database.SqlQuery(
                      "GetCustomers @Name, @ZipCode",
                      new SqlParameter("@Name", ""),
                      new SqlParameter("@ZipCode", "12345")
                  )
Naresh Seth8y ago
Thank you all for your precious time and solution!

I did put "If Statement" in Stored procedure and it worked fine.
Jignesh Kumar8y ago
Two select statement not required as I told erlier you can write like this
CREATE PROCEDURE [dbo].[GetCustomers]

@Name nvarchar(max) = null,

@ZipCode int = null

AS

Begin

SELECT [Id], [Name],[ZipCode] FROM [dbo].[Customers] WHERE [Name] LIKE '%' +@Name +'%'

and [ZipCode] = @ZipCode;

End

cs.GetCustomersByName("Jo%", DBNull.Value);
cs.GetCustomersByZip(DBNull.Value, 98052);

And one more thing you need to look at here

GetCustomersByName(string name=null, int? zipcode=null)

It may solve you problem..
Naresh Seth8y ago
Hi All,

thanks for putting your valued comments. I tried passing null to one parameter, But the second is not returning anything. I am getting the result of the first select.
CREATE PROCEDURE [dbo].[GetCustomers]
@Name nvarchar(max) = NULL,
@ZipCode VARCHAR(50) = NULL
AS
IF (ISNULL(@Name,'') <> '')
SELECT [Id], [Name],[ZipCode] FROM [dbo].[Customers] WHERE [Name] LIKE (@Name);
IF (ISNULL(@ZipCode,'') <> '')
SELECT [Id], [Name],[ZipCode] FROM [dbo].[Customers] WHERE [ZipCode] = @ZipCode;
GO
cs.GetCustomersByName("Jo%", "");
cs.GetCustomersByZip("", 98052);
Nitin Sontakke8y ago
Further to what Jignesh has mentioned, why have Zip parameter to the GetCurstomerByName and Name parameter to the GetCustomerByZipCode.

It just adds to the confusion.
Jignesh Kumar8y ago
As per your method calling if you pass both parameter then you no need to write two select statement. you can write like this because you are using both parameter just need to pass one parameter with value and another pass as null
CREATE PROCEDURE [dbo].[GetCustomers]

@Name nvarchar(max) = null,

@ZipCode int = null

AS

Begin

SELECT [Id], [Name],[ZipCode] FROM [dbo].[Customers] WHERE [Name] LIKE '%' +@Name +'%'

and [ZipCode] = @ZipCode;

End
if you passing name as "Na%' and pincode as 87587 then it will give wrong out put if you have Two select statement based on null condition. if you are cheking name not null then there is no use of passing pincode becasue it will give wrong impression it will seach based on name only but we have passed both parameter.

You can call you method as below by passing one value as null

cs.GetCustomersByName("Na%", null); -- Pass ZipCode as null
cs.GetCustomersByZipCode(null, 87587); -- Pass Name as null
Nitin Sontakke8y ago
To put it in different what Jignesh has already pointed out, there is no way for SQL Server to know your intentions, unless you make them explicit in some way or another.

In any case you have two different methonds and you are passing both the parameters to those methods.

However, within the code of these two different methods, you can pass null to other parameter. For example, pass null to name in by zip and pass null to zip in by name method.

The third and much uglier option is to have a third string parameter and pass the value 'name' or 'zip' to it and check this parameter's value and execute one of the two statements as the case may be.
Packiaraj Santhiyagu8y ago
Hi,
Please check your param, if one the param is null do not run that query.
CREATE PROCEDURE [dbo].[GetCustomers]
    @Name nvarchar(max) = NULL,
    @ZipCode VARCHAR(50) = NULL
AS

IF (ISNULL(@Name,'') <> '')
    SELECT [Id], [Name],[ZipCode] FROM [dbo].[Customers] WHERE [Name] LIKE (@Name);
IF (ISNULL(@ZipCode,'') <> '')
    SELECT [Id], [Name],[ZipCode] FROM [dbo].[Customers] WHERE [ZipCode] = @ZipCode;
GO
Naresh Seth8y ago
Hi Jingesh,

I am trying to pass both parameters(not null). is there any way to just execute the first select statement or second select statement specifically.

cs.GetCustomersByName("Na%", 87587); // Calling method to search by Name in table and retrieve the details

cs.GetCustomersByZip("Na%", 868797); // Calling method to search by Zipcode in table and retrieve the details

CREATE PROCEDURE [dbo].[GetCustomers] @Name nvarchar(max), @ZipCode int
AS
SELECT [Id], [Name],[ZipCode] FROM [dbo].[Customers] WHERE [Name] LIKE (@Name);
SELECT [Id], [Name],[ZipCode] FROM [dbo].[Customers] WHERE [ZipCode] = @ZipCode;

Above two methods are calling the same procedure. But i want them to use different SELECT statement.
Jignesh Kumar8y ago
Hi Naresh,

Could you please exaplain bit more on your question. Only one select statement you want to execute base on parameter is not null or you mean which way ?
Faisal Pathan8y ago
you can write like if @name != '' then fire first qry and vice-versa