Parameterized stored procedures

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

Transact-SQL (2012)

Parameterized stored procedures

Author

Topic

idiviana
Starting Member

6 Posts

Posted - 2014-02-24 : 10:13:39

I have two listboxes.Wenn I select any item from first listbox,subitems appear in the second listbox.I assigned the selecteditems(from listbox 1-2) to two Textboxes.I want to call(to insert,delete) any selected item from database.For this I have written two stored procedures. But I can't give parameter for table and columm names. It works only wenn I give just one parameter.I don't want select from or insert into certain table or column in the code, contrarily it can be valid for any selected items.Is this possible?How can I solve this problem?

USE envanter
GO
CREATE PROC SP$select
@p1 NVARCHAR(MAX)
@p2 NVARCHAR(MAX) //gives error
AS
BEGIN
SELECT @p1
FROM @p2 //gives error
END

USE envanter
GO
CREATE PROC SP$insert(
@p3 AS VARCHAR(MAX)
@p2 AS VARCHAR(MAX)//gives error
@p1 NVARCHAR(MAX) // gives error
)
AS
BEGIN
INSERT INTO @p2(@p1) VALUES(@p3)
END

calling from C#:

cmd.CommandText = "SP$select";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@p2",textBox2.Text));
cmd.Parameters.Add(new SqlParameter("@p1",textBox1.Text));
cmd.Connection = baglan;
baglan.Open();
cmd.ExecuteNonQuery();
baglan.Close();

cmd.CommandText = "SP$insert";
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add(new SqlParameter("@p1",f1.textBox1.Text));
//cmd.Parameters.Add(new SqlParameter("@p2",f1.textBox2.Text));
cmd.Parameters.Add(new SqlParameter("@p3",textBox1.Text));
cmd.Connection = baglan;
baglan.Open();
cmd.ExecuteNonQuery();
baglan.Close();Text

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-24 : 12:48:57

I'm sure you have your reasons, but you cannot use Object names as parameter/variables like that. The only way to do that is to make a stored procedure/function for each combination or use Dynamic SQL.

Here is a link that discusses Dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html

Here is a link that is more specific to your ask:
http://stackoverflow.com/questions/12467897/sql-server-passing-table-name-as-parameter-to-insert-into-a-variable-table

If this is an educational exercise then great, but I'd highly recommend avoiding this approach for production.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-24 : 13:23:27

Separate your procedure parameters with comma.

CREATE PROC [SP$select]
@p1 NVARCHAR(MAX),
@p2 NVARCHAR(MAX)
AS

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX) = N'SELECT ' + QUOTENAME(@p1) + N' FROM ' + QUOTENAME(@p2) + N';';

EXEC (@SQL);

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

idiviana
Starting Member

6 Posts

Posted - 2014-02-25 : 10:00:21

Thanks for the answers.I am trying to make a stored procedure for each situation.But it doesn't work.

USE envanter
GO
CREATE PROC [SP$insert](
@p3 AS VARCHAR(MAX)
)
AS
BEGIN

IF EXISTS(SELECT sira_no FROM Ana)
BEGIN
INSERT INTO Ana(sira_no) VALUES(@p3)
END
ELSE

IF EXISTS(SELECT marka_id FROM Ana)
BEGIN
INSERT INTO Ana(marka_id) VALUES(@p3)
END
ELSE
.
.
.
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-25 : 11:31:54

@p3 is a comma separated string, right?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

idiviana
Starting Member

6 Posts

Posted - 2014-02-26 : 08:31:23

This is the last form but it still doesn't work.I couldn't solve it.

USE env
GO
CREATE PROC [SP$select1]
@p1 NVARCHAR(MAX)
AS
SET NOCOUNT ON

IF(@p1='Sira No')
BEGIN
SELECT sira_no FROM Ana
END
ELSE

IF(@p1='Model ID')
BEGIN
SELECT model_id FROM Model
END
ELSE
.
.
.

USE env
GO
CREATE PROC [SP$insert](
@p2 AS VARCHAR(MAX)
)
AS
BEGIN
SET NOCOUNT ON
IF EXISTS(SELECT sira_no FROM Ana)
BEGIN
INSERT INTO Ana(sira_no) VALUES(@p2)
END
ELSE

IF EXISTS(SELECT model_id FROM Ana)
BEGIN
INSERT INTO Ana(model_id) VALUES(@p2)
END
ELSE
.
.
END

calling from C#:

if (listBox1.SelectedIndex == 1)
{
cmd.CommandText = "SP$select1";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@p1", textBox1.Text));
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
else
if (listBox1.SelectedIndex == 2)
{.
.
.

try
{
cmd.CommandText = "SP$insert";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@p2", textBox1.Text));
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Inserted!");
}
catch (Exception)
{
conn.Close();
MessageBox.Show("Not inserted!");
.
.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-26 : 12:27:42

You can't do that. Please see the links I posted on 02/24/2014 : 12:48:57

idiviana
Starting Member

6 Posts

Posted - 2014-02-27 : 09:38:27

Now is it OK? But how can I add the column name as p1 in [SP$insert]?
Wenn I've made so, it didn't work too.

set @sql = 'INSERT INTO Ana(sira_no) VALUES(' + @p3 + ')'

use env
go
create proc [SP$insert](
@p1 nvarchar(100),
@p2 nvarchar(100),
@p3 nvarchar(100),
@debug bit = 0)
as
begin
set nocount on
if (exists(select sira_no from Ana))
declare @sql nvarchar(100)
set @sql = N'INSERT INTO ' + QUOTENAME(@p2) + N' VALUES ' + QUOTENAME(@p3) + N';';
if @debug = 1 PRINT @sql
exec sp_executesql @sql
end
go

use env
go
create proc [SP$select](
@p1 nvarchar(100),
@p2 nvarchar(100),
@debug bit = 0)
as
begin
set nocount on
declare @sql nvarchar(100) = N'SELECT ' + QUOTENAME(@p1) + N' FROM ' + QUOTENAME(@p2) + N';';
IF @debug = 1 PRINT @sql
exec sp_executesql @sql
end
go

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-27 : 11:23:32

You need to provide more information. You said: "Wenn I've made so, it didn't work too." How did it not work? Did SQL throw an error, if so what error? Did you not get any data back?

I only looked at your SP$select sproc, so it is possible there are errors in the other ones. However, that sproc looks more-or-less correct. The only potential issue I see is that if you qualify your table name with a schema QUOTENAME will mess it up. Meaning if you pass "dbo.TableName", QUOTENAME will wrap the entire string with square-backets "[dbo.TableName]", which will cause an "Invalid Object Name" error.

idiviana
Starting Member

6 Posts

Posted - 2014-02-27 : 14:47:37

I mean,it gives an error,that the data couldn't be inserted into the database. Sproc-select doesn't throw any exception. I will try again. Thanks.

quote:
Originally posted by Lamprey

You need to provide more information. You said: "Wenn I've made so, it didn't work too." How did it not work? Did SQL throw an error, if so what error? Did you not get any data back?

I only looked at your SP$select sproc, so it is possible there are errors in the other ones. However, that sproc looks more-or-less correct. The only potential issue I see is that if you qualify your table name with a schema QUOTENAME will mess it up. Meaning if you pass "dbo.TableName", QUOTENAME will wrap the entire string with square-backets "[dbo.TableName]", which will cause an "Invalid Object Name" error.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-27 : 15:05:39

The INSERT is missing parenthesis. You need to wrap the VALUES in parenthesis.

N'INSERT INTO ' + QUOTENAME(@p2) + N' VALUES(' + QUOTENAME(@p3) + N');';

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-27 : 15:07:24

It will still not help if @p3 is a comma-separated list like 1,2,3.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources