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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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!");
.
.

Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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.

Go to Top of Page

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');';
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -