SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Parameterized stored procedures
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

idiviana
Starting Member

6 Posts

Posted - 02/24/2014 :  10:13:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 02/24/2014 :  12:48:57  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 02/24/2014 :  13:23:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 02/25/2014 :  10:00:21  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 02/25/2014 :  11:31:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
@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 - 02/26/2014 :  08:31:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 02/26/2014 :  12:27:42  Show Profile  Reply with Quote
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 - 02/27/2014 :  09:38:27  Show Profile  Reply with Quote
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

Edited by - idiviana on 02/27/2014 10:27:18
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 02/27/2014 :  11:23:32  Show Profile  Reply with Quote
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 - 02/27/2014 :  14:47:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 02/27/2014 :  15:05:39  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 02/27/2014 :  15:07:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000