convert to Strored Proc... - SQL Server Forums

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

Transact-SQL (2005)

convert to Strored Proc...

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-13 : 13:26:06

i tried to put this in Stored proc. but for some reason my code does not seem to work (inline statement like below works.) (I got the solution from http://www.codeproject.com/KB/webforms/EditNestedGridView.aspx).

conn = new SqlConnection(@"Server=Purell; Integrated Security=SSPI; Database=myPurell");
strQRY = @"SELECT G.Generic_Name + ' (' + G.Generic_Description + ')' as Name,
I.Inventory_ID,
I.Inventory_Part as Part,
I.Inventory_Serial as Serial,
DATEDIFF(day,GETDATE(),I.Inventory_Expiration) as DaysToExpire,
convert(varchar(10),I.Inventory_Expiration,101) as Expiration,
I.Inventory_Available_Quantity as Quantity,
dbo.Inventory_LocationString(I.Location_ID) AS Location,
F.Flight_Name as Flight,
S.Status_Name as Status,
L.Location_Parent as LocationID
FROM [dbo].[Brand] B INNER JOIN
dbo.Inventory_Formulary I_F ON B.Brand_ID = I_F.Brand_ID RIGHT OUTER JOIN
dbo.Inventory I INNER JOIN
dbo.Generic G ON I.Generic_ID = G.Generic_ID INNER JOIN
dbo.Location L ON I.Location_ID = L.Location_ID INNER JOIN
dbo.Status S ON I.Status_ID = S.Status_ID INNER JOIN
dbo.Containers C ON I.Container_ID = C.Container_ID INNER JOIN
dbo.Agency A ON I.Agency_ID = A.Agency_ID INNER JOIN
dbo.Generic_Type GT ON G.Generic_Type_ID = GT.Generic_Type_ID INNER JOIN
dbo.Flight F ON C.Flight_ID = F.Flight_ID INNER JOIN
dbo.Kit K ON C.Kit_ID = K.Kit_ID INNER JOIN
dbo.Manufacturer M ON I.Manufacturer_ID = M.Manufacturer_ID ON
I_F.Inventory_ID = I.Inventory_ID
WHERE L.Location_Parent= @lpID AND
I.Inventory_Available_Quantity " + qEqual + " @qty AND DATEDIFF(day,GETDATE(),I.Inventory_Expiration) " + eEqual + " @ddlExp AND G.Generic_Name like @nameSearch " + strSort;

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-13 : 13:45:12

can you post your sproc please? does not seem to work? do you get an error? if so post that too. how are you calling/executing your sproc from this what seems to be an asp page.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-13 : 13:59:48

private DataTable ChildDataSource(string strlpID, string strSort)
{
SqlConnection conn;
SqlDataAdapter daProducts;
DataTable dtProducts;

string strQRY = "";
string qEqual = ">";
string eEqual = ">";
int quantity = 0;
int exp = 0;
string nameSearch = "%";

if (string.IsNullOrEmpty(txtQuantity.Text))
{
}
else
{
quantity = int.Parse(txtQuantity.Text);
if (string.IsNullOrEmpty(ddlQTY1.Text))
{
//qEqual = ">";
}
else
{
qEqual = ddlQTY1.Text;
}
}

if (string.IsNullOrEmpty(ddlExp2.Text))
{
}
else
{
exp = int.Parse(ddlExp2.Text);

if (string.IsNullOrEmpty(ddlExp1.Text))
{ }
else
{
eEqual = ddlExp1.Text;
}
}

if (string.IsNullOrEmpty(txtNameSearch.Text))
{ }
else
{
nameSearch = txtNameSearch.Text + "%";
}

======= PREVIOUSLY POSTED INLINE STATEMENT GOES HERE =======

SqlCommand cmd = new SqlCommand(strQRY, conn);

SqlParameter paraQty = new SqlParameter();
paraQty.ParameterName = "@qty";
paraQty.SqlDbType = SqlDbType.Int;
paraQty.Direction = ParameterDirection.Input;
paraQty.Value = quantity;

SqlParameter paraLPID = new SqlParameter();
paraLPID.ParameterName = "@lpID";
paraLPID.SqlDbType = SqlDbType.VarChar;
paraLPID.Direction = ParameterDirection.Input;
paraLPID.Value = strlpID;

SqlParameter paraExp = new SqlParameter();
paraExp.ParameterName = "@ddlExp";
paraExp.SqlDbType = SqlDbType.Int;
paraExp.Direction = ParameterDirection.Input;
paraExp.Value = exp;

SqlParameter paraNameSearch = new SqlParameter();
paraNameSearch.ParameterName = "@nameSearch";
paraNameSearch.SqlDbType = SqlDbType.VarChar;
paraNameSearch.Direction = ParameterDirection.Input;
paraNameSearch.Value = nameSearch;

cmd.Parameters.Add(paraQty);
cmd.Parameters.Add(paraLPID);
cmd.Parameters.Add(paraExp);
cmd.Parameters.Add(paraNameSearch);
daProducts = new SqlDataAdapter(cmd);
dtProducts = new DataTable();
daProducts.Fill(dtProducts);

return dtProducts;
}

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-13 : 14:06:22

I am trying to create a stored procedure (with optional parameter)...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE SP_CHILDDATASOURCE
@QTY INT = NULL,
@LPID VARCHAR(50) = NULL,
@DDLEXP INT = NULL,
@NAMESEARCH VARCHAR(100) = NULL,
@QEQUAL CHAR(1) = NULL,
@STRSORT CHAR(1) = NULL,
@EEQUAL CHAR(1) = NULL

AS
BEGIN
SET NOCOUNT ON;

SELECT G.Generic_Name + ' (' + G.Generic_Description + ')' [Name],
I.Inventory_ID,
I.Inventory_Part as Part,
I.Inventory_Serial as Serial,
DATEDIFF(day,GETDATE(),I.Inventory_Expiration) as DaysToExpire,
convert(varchar(10),I.Inventory_Expiration,101) as Expiration,
I.Inventory_Available_Quantity as Quantity,
dbo.Inventory_LocationString(I.Location_ID) AS Location,
F.Flight_Name as Flight,
S.Status_Name as [Status],
L.Location_Parent as LocationID
FROM [dbo].[Brand] B INNER JOIN
dbo.Inventory_Formulary I_F ON B.Brand_ID = I_F.Brand_ID RIGHT OUTER JOIN
dbo.Inventory I INNER JOIN
dbo.Generic G ON I.Generic_ID = G.Generic_ID INNER JOIN
dbo.Location L ON I.Location_ID = L.Location_ID INNER JOIN
dbo.Status S ON I.Status_ID = S.Status_ID INNER JOIN
dbo.Containers C ON I.Container_ID = C.Container_ID INNER JOIN
dbo.Agency A ON I.Agency_ID = A.Agency_ID INNER JOIN
dbo.Generic_Type GT ON G.Generic_Type_ID = GT.Generic_Type_ID INNER JOIN
dbo.Flight F ON C.Flight_ID = F.Flight_ID INNER JOIN
dbo.Kit K ON C.Kit_ID = K.Kit_ID INNER JOIN
dbo.Manufacturer M ON I.Manufacturer_ID = M.Manufacturer_ID ON
I_F.Inventory_ID = I.Inventory_ID
WHERE L.Location_Parent= @lpID AND
I.Inventory_Available_Quantity " & @qEqual & " @qty AND DATEDIFF(day,GETDATE(),I.Inventory_Expiration) " & @eEqual & " @ddlExp AND G.Generic_Name like @nameSearch " & @strSort;

END
GO

I am getting following error when create this SP....

Msg 102, Level 15, State 1, Procedure SP_CHILDDATASOURCE, Line 40
Incorrect syntax near ' & @qEqual & '.
Msg 105, Level 15, State 1, Procedure SP_CHILDDATASOURCE, Line 40
Unclosed quotation mark after the character string ' & @strSort;

END
GO

'.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-13 : 14:22:57

well you cannot use " in SQL to concatenate. what are these two @eEqual and @ddlExp what value can they contain or are they always the same value or optional?
the same with @strSort. post possible contents of these vars
<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-13 : 14:30:51

try this, you will have to fix up any problem since i do not have your table structures in my db


CREATE PROCEDURE SP_CHILDDATASOURCE 
@QTY INT = NULL,
@LPID VARCHAR(50) = NULL,
@DDLEXP INT = NULL,
@NAMESEARCH VARCHAR(100) = NULL,
@QEQUAL CHAR(1) = NULL,
@STRSORT CHAR(1) = NULL,
@EEQUAL CHAR(1) = NULL

AS
BEGIN
SET NOCOUNT ON;

DECLARE @sql nvarchar(4000),                                
        @paramlist  nvarchar(4000)

SELECT @sql =
'SELECT g.generic_name + '' ('' + g.generic_description + '')'' [Name], 
       i.inventory_id, 
       i.inventory_part                                    AS part, 
       i.inventory_serial                                  AS serial, 
       Datediff(DAY,Getdate(),i.inventory_expiration)      AS daystoexpire, 
       Convert(VARCHAR(10),i.inventory_expiration,101)     AS expiration, 
       i.inventory_available_quantity                      AS quantity, 
       dbo.Inventory_locationstring(i.location_id)         AS location, 
       f.flight_name                                       AS flight, 
       s.status_name                                       AS [Status], 
       l.location_parent                                   AS locationid 
FROM   [dbo].[Brand] b 
       INNER JOIN dbo.inventory_formulary i_f 
         ON b.brand_id = i_f.brand_id 
       RIGHT OUTER JOIN dbo.inventory i 
        INNER JOIN dbo.generic g 
          ON i.generic_id = g.generic_id 
        INNER JOIN dbo.location l 
          ON i.location_id = l.location_id 
        INNER JOIN dbo.status s 
          ON i.status_id = s.status_id 
        INNER JOIN dbo.containers c 
          ON i.container_id = c.container_id 
        INNER JOIN dbo.agency a 
          ON i.agency_id = a.agency_id 
        INNER JOIN dbo.generic_type gt 
          ON g.generic_type_id = gt.generic_type_id 
        INNER JOIN dbo.flight f 
          ON c.flight_id = f.flight_id 
        INNER JOIN dbo.kit k 
          ON c.kit_id = k.kit_id 
        INNER JOIN dbo.manufacturer m 
          ON i.manufacturer_id = m.manufacturer_id 
         ON i_f.inventory_id = i.inventory_id 
WHERE  1 = 1 '

IF @LPID IS NOT NULL                                       
   SELECT @sql = @sql + ' AND L.Location_Parent= @lpID'	

IF @QTY IS NOT NULL 
	BEGIN
		SELECT @sql = @sql + ' AND I.Inventory_Available_Quantity ' +  @qEqual +  ' @qty '
	END

IF @nameSearch IS NOT NULL 
	BEGIN
		SELECT @sql = @sql + ' AND G.Generic_Name like @nameSearch + ''%'''
	END


IF @ddlExp IS NOT NULL 
	BEGIN
		SELECT @sql = @sql + ' AND DATEDIFF(day,GETDATE(),I.Inventory_Expiration) ' +  @eEqual  +  ' @ddlExp '
	END


IF @strSort IS NOT NULL 
	BEGIN
		SELECT @sql = @sql + ' ORDER BY ' + @strSort
	END

--For debugging purposes
PRINT @sql

SELECT @paramlist = '@QTY  INT, @LPID VARCHAR(50), @DDLEXP INT, @NAMESEARCH VARCHAR(100),
                     @QEQUAL CHAR(1), @STRSORT CHAR(1),@EEQUAL CHAR(1)'  

EXEC sp_executesql @sql, @paramlist, @QTY, @LPID, @DDLEXP, @NAMESEARCH, @QEQUAL, @STRSORT, @EEQUAL

END
GO

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-13 : 15:28:09

Jo Solo,

It worked like charmed. At least when create the Stored Proc. I am still in testing phase. I changed the @strsort char(1) to char(4) for because I will be passing "ASC" or "DESC".

Thanks A MILLION FOR YOUR HELP AND TIME..... YOU ROCK......

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-13 : 15:38:54

glad to help. I am just passing on all the rocking stuff I learned from this site.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-13 : 15:40:45

DECLARE @RC int
DECLARE @QTY int
DECLARE @LPID varchar(50)
DECLARE @DDLEXP int
DECLARE @NAMESEARCH varchar(100)
DECLARE @QEQUAL char(1)
DECLARE @EEQUAL char(1)
DECLARE @STRSORT char(4)

-- TODO: Set parameter values here.

EXECUTE @RC = [HITT].[dbo].[SP_CHILDDATASOURCE]
@QTY
,46
,@DDLEXP
,@NAMESEARCH
,@QEQUAL
,@EEQUAL
,'desc'

I am getting an error...

/*------------------------
DECLARE @RC int
DECLARE @QTY int
DECLARE @LPID varchar(50)
DECLARE @DDLEXP int
DECLARE @NAMESEARCH varchar(100)
DECLARE @QEQUAL char(1)
DECLARE @EEQUAL char(1)
DECLARE @STRSORT char(4)

-- TODO: Set parameter values here.

EXECUTE @RC = [HITT].[dbo].[SP_CHILDDATASOURCE]
@QTY
,46
,@DDLEXP
,@NAMESEARCH
,@QEQUAL
,@EEQUAL
,'desc'
------------------------*/
SELECT g.generic_name + ' (' + g.generic_description + ')' [Name],
i.inventory_id,
i.inventory_part AS part,
i.inventory_serial AS serial,
Datediff(DAY,Getdate(),i.inventory_expiration) AS daystoexpire,
Convert(VARCHAR(10),i.inventory_expiration,101) AS expiration,
i.inventory_available_quantity AS quantity,
dbo.Inventory_locationstring(i.location_id) AS location,
f.flight_name AS flight,
s.status_name AS [Status],
l.location_parent AS locationid
FROM [dbo].[Brand] b
INNER JOIN dbo.inventory_formulary i_f
ON b.brand_id = i_f.brand_id
RIGHT OUTER JOIN dbo.inventory i
INNER JOIN dbo.generic g
ON i.generic_id = g.generic_id
INNER JOIN dbo.location l
ON i.location_id = l.location_id
INNER JOIN dbo.status s
ON i.status_id = s.status_id
INNER JOIN dbo.containers c
ON i.container_id = c.container_id
INNER JOIN dbo.agency a
ON i.agency_id = a.agency_id
INNER JOIN dbo.generic_type gt
ON g.generic_type_id = gt.generic_type_id
INNER JOIN dbo.flight f
ON c.flight_id = f.flight_id
INNER JOIN dbo.kit k
ON c.kit_id = k.kit_id
INNER JOIN dbo.manufacturer m
ON i.manufacturer_id = m.manufacturer_id
ON i_f.inventory_id = i.inventory_id
WHERE 1 = 1 AND L.Location_Parent= @lpID ORDER BY desc
Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'desc'.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-13 : 15:44:04

yes but which 'field' DESC you need to provde a field name

EXECUTE @RC = [HITT].[dbo].[SP_CHILDDATASOURCE]
@QTY
,46
,@DDLEXP
,@NAMESEARCH
,@QEQUAL
,@EEQUAL
,'Name desc'

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-13 : 16:24:56

Thank you sir. That's my bad.... I have like 6 things going on at the same time and didnt pay attention.

Again, Thanks a lot..... Appreciate it

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-13 : 17:16:08

6 things? that's all you got going :) no problem!

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-19 : 15:49:50

Is there any danger using sp_executesql if users dont have right to read the pertaining tables?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-19 : 16:47:25

If the user doesn't have permissions on the table, then sp_executesql will not work.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-19 : 18:20:05

what if user passes a sql statement (i.e. delete from table1) like delete through parameter. There are two parameters where users can key in whatever they need to search.

I was told that sp_executesql can be dangerous since the statement like that can pass through. Any other alternative to this...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-19 : 19:39:55

The user still needs permission to the underlying table. It will fail otherwise, doesn't matter if it's via sp_executesql or not.

sp_executesql is dangerous if you allow any query to be passed to a stored procedure. That is very bad coding. What if someone passed in DELETE FROM MostImportantTable or maybe DROP DATABASE MostImportantDatabase?

I haven't read through this topic enough to be able to tell you if there's an alternative. It's not often things need to be dynamic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-19 : 20:59:34

Tara, thanks for your input. So, what's the fix for sp_executesql where optional parameters are fed...

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-19 : 22:05:56

quote:
Originally posted by doran_doran

Tara, thanks for your input. So, what's the fix for sp_executesql where optional parameters are fed...

Well, don't you have a finite number of possible columns that need to be "searched"?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-19 : 22:07:44

Yes, six columns.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 10:58:58

quote:
Originally posted by doran_doran

Yes, six columns.

Read this

http://weblogs.sqlteam.com/brettk/archive/2004/05/05/1312.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources