how to pass parameter to sql procedure

Question

0.00/5 (No votes)

See more:

hello

i need some help with passing List values (or so I believe it's List :) ) to parameter value of sql procedure I have in sql server

trying to explain:

I have datagridview with one column called sampleID, I have 10 rows of data in datagridview and in sampleID i have single values of numbers

I have textBox1 where user which is logged on windows form holds numbers which he is allowed to see in the form, and let's say that I can only see rows that holds 3 and 7
so in textBox1 I divided it like this

,3,,7,

now my sql procedure is simple

SQL

USE [FreeDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[sample_proc]

@PageSize int output, 
@PageNum int output,
@sampleID varchar(MAX) output

as

Declare @RowStart int 
Declare @RowEnd int 

--SELECT	@PageSize = 50,
	--@PageNumber = 1
	
if @PageNum > 0 
Begin 

SET @PageNum = @PageNum -1 

SET @RowStart = @PageSize * @PageNum + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

With Cust AS 
     ( SELECT *, 
       ROW_NUMBER() OVER (order by ID DESC) as RowNumber 
       FROM Sample WHERE sampleID = @sampleID )

select * 
from Cust 
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end

and in my windows form where datagridview is I have

C#

page.Text = "1";
                size.Text = "25";

                SqlCommand command = new SqlCommand("sample_proc", cs);
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter param = new SqlParameter("@PageNum", SqlDbType.Int);
                SqlParameter param1 = new SqlParameter("@PageSize", SqlDbType.Int);
                SqlParameter param2 = new SqlParameter("@sampleID", SqlDbType.VarChar, -1);

                param.Direction = ParameterDirection.InputOutput;
                param1.Direction = ParameterDirection.InputOutput;
                param2.Direction = ParameterDirection.InputOutput;

                param.Value = page.Text;
                param1.Value = size.Text;

                string[] sm1 = textBox1.Text.Split(',');

                foreach (string value in sm1)
                {                    
                    param2.Value = value;
                }
                
                command.Parameters.Add(param);
                command.Parameters.Add(param1);
                command.Parameters.Add(param2);

                daSample.SelectCommand = command;

                dsSample.Clear();
                daSample.Fill(dsSample, "Sample");

                SampleBS.DataSource = dsSample.Tables["Sample"];

                datagridview.DataSource = SampleBS;

but when I run it I get nothing, empty

anyone :)

Posted 19-Apr-13 8:22am

shonezi

Updated 19-Apr-13 9:40am

Maciej Los

v2

Add a Solution

Comments

ZurdoDev 19-Apr-13 15:23pm

That's a lot to read through, but SQL does not have lists, so you send in a delimited list of ids as a varchar.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-04-19T09:59:00

Solution 1

After that line:

C#

command.Parameters.Add(param2);

add:

C#

SqlDataReader reader = command.ExecuteReader();
DataTable dt = new DataTable("Sample");
dt.Load(reader);
datagridview.DataSource = dt

Of course, you can use SelectCommand[^] and SelectParameters[^] too. ;)

Posted 19-Apr-13 9:59am

Maciej Los

José Amílcar Casimiro · Answer 2 · 2013-04-19T10:02:00

Solution 2

Hello,

Check this thread, it may help you:
http://stackoverflow.com/questions/1069311/passing-an-array-of-parameters-to-a-stored-procedure[^]

JAFC

Posted 19-Apr-13 10:02am

José Amílcar Casimiro

Matt Puleston · Answer 3 · 2014-11-05T01:06:00

Hello,

Heres how I handle a list of Ids as a parameter in SQL. First I have a function that converts a delimiter seperated string of Id's into a table.

SQL

CREATE FUNCTION fnGetNumericTableFromCommaSeparatedList
(
	@CommaSeparatedList VARCHAR(4000),
	@Delimiter CHAR
)
RETURNS @ListValues TABLE 
(
	Id BIGINT NULL
)
AS
BEGIN

DECLARE @CommaIndex INT
DECLARE @TextValue VARCHAR(255)

SET @CommaIndex = CHARINDEX(@Delimiter, @CommaSeparatedList)

WHILE @CommaIndex > 0
BEGIN					 
	SET @TextValue = LTRIM(RTRIM(SUBSTRING(@CommaSeparatedList, 1, @CommaIndex - 1)))
	
	IF(@TextValue != '' AND (ISNUMERIC(@TextValue) = 1))
	BEGIN			 
		INSERT @ListValues (Id) VALUES (CONVERT(BIGINT, @TextValue))
	END
	 
	IF(@TextValue = 'NULL')
	BEGIN
		INSERT @ListValues (Id) VALUES (NULL)
	END 
	
	SET @CommaSeparatedList = SUBSTRING(@CommaSeparatedList, @CommaIndex + 1, LEN(@CommaSeparatedList)) 
	 
	SET @CommaIndex = CHARINDEX(@Delimiter, @CommaSeparatedList)
END
	
IF (@CommaSeparatedList != '' AND (ISNUMERIC(@CommaSeparatedList) = 1))
BEGIN
	INSERT INTO @ListValues (Id) VALUES (CONVERT(BIGINT, @CommaSeparatedList))
END	
	
IF(@CommaSeparatedList = 'NULL')
BEGIN
	INSERT @ListValues (Id) VALUES (NULL)
END	

RETURN

END

Then I join to the function

SQL

DECLARE @ListOfIds VARCHAR(255)
SET @ListOfIds = '1, 3, 5, 6, 6'

SELECT		f.*
FROM		Foo f
INNER JOIN	dbo.fnGetNumericTableFromCommaSeparatedList(@ListOfIds, ',') l
   ON	        f.Id = l.Id

Hope that helps,
Matt

Laiju k · Answer 4 · 2014-11-05T01:15:00

Solution 4

Quote:
@PageSize int output,
@PageNum int output,
@sampleID varchar(MAX) output

why are you using this.You are passing parameter as input not as output

Posted 5-Nov-14 1:15am

Laiju k