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 2008 Forums
 Transact-SQL (2008)
 Converting Dynamic to SPROC

Author  Topic 

tech_1
Posting Yak Master

129 Posts

Posted - 2013-02-07 : 15:21:11
So I am going to begin to converting dynamically generated SQL into a SPROC.
unfortunately the dynamic SQL has different routes/branches to include certain tables and so on depending on condition.
Not only that, there are also some LIKE searches where if the input contains a wildchar (*) then it will do a LIKE otherwise an equal.

There are also many input fields ranging from DateTime to dropdownlists and free textboxes.

Does anyone know anything about the best way of converting this without much pain? Something maybe I dont know about or do I have to spend literally days and weeks converting this manually?

Any tips or tricks or advice about doing this type of conversion?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-07 : 16:00:14
This probably doesn't help, but why do you want to convert it from Dynamic SQL? Is it just because someone said dynamic sql is bad?

There is no automated way to do a conversion. If you are doing dynamic sql that do catch-all queries then there is a very high possibility that dynamic sql is going to be more efficient.

If you are not doing catch-all queries then it may make sense to convert it to regular non-dynamic sql.

The only other advice I can offer, without knowing more info, would be that you could use PRINT (or something similar) to output the actual query begin created that you can cut-n-paste.
Go to Top of Page

tech_1
Posting Yak Master

129 Posts

Posted - 2013-02-07 : 16:16:31
yes I was thinking about the cut and paste thing.

so I dont like dynamic sql because its slow and pain to maintain and prefer everything to be seperate. Thats why - for cost and maintainence reasons as well as security. It is also prone to SQL injection and so on as you know yourself. yes you could do filtering blah blah but at the end of the day - maintainence headache and the DBA and IT security team are not happy with how this is already working and its my job to convert it.

to me that looks like the SPROC is going to be easily more than 500 lines! need to check for null values for the parameters to see which "branch" to execute then to see if there is a wild card and do a like instead of an equals and so on.... *sigh*
Go to Top of Page

tech_1
Posting Yak Master

129 Posts

Posted - 2013-02-10 : 21:10:33
So I have this dynamic generated SQL and want to convert it to a SPROC. Not sure how to do this in a smart way:

quote:

dim strGen
strGen = ""
if trim(strControlNum) <> "" then
if instr(strControlNum,"*") > 0 then
strGen = strGen & "AND Control.ControlNumber LIKE '" & replace(strControlNum,"*","%") & "' "
else
strGen = strGen & "AND Control.ControlNumber = '" & strControlNum & "' "
end if
end if
if trim(strMfr) <> "" then
if instr(strMfr,"*") > 0 then
strGen = strGen & "AND Manufacturer.MfrName LIKE '" & replace(strMfr,"*","%") & "' "
else
strGen = strGen & "AND Manufacturer.MfrName = '" & strMfr & "' "
end if
end if
if trim(strModel) <> "" then
if instr(strModel,"*") > 0 then
strGen = strGen & "AND Model.ModelNumber LIKE '" & replace(strModel,"*","%") & "' "
else
strGen = strGen & "AND Model.ModelNumber = '" & strModel & "' "
end if
end if
if trim(strDesc) <> "" then
if instr(strDesc,"*") > 0 then
strGen = strGen & "AND Model.ModelDescription LIKE '" & replace(strDesc,"*","%") & "' "
else
strGen = strGen & "AND Model.ModelDescription = '" & strDesc & "' "
end if
end if
if trim(strSerial) <> "" then
if instr(strSerial,"*") > 0 then
strGen = strGen & "AND Control.ControlSerialNumber LIKE '" & replace(strSerial,"*","%") & "' "
else
strGen = strGen & "AND Control.ControlSerialNumber = '" & strSerial & "' "
end if
end if
if trim(strUnitID) <> "" then
if instr(strUnitID,"*") > 0 then
strGen = strGen & "AND (Control.ControlItemID LIKE '" & replace(strUnitID,"*","%") & "') "
else
strGen = strGen & "AND (Control.ControlItemID = '" & strUnitID & "') "
end if
end if
if trim(strBC) <> "" then
if instr(strBC,"*") > 0 then
strGen = strGen & "AND (Control.ControlBarcode LIKE '" & replace(strBC,"*","%") & "') "
else
strGen = strGen & "AND (Control.ControlBarcode = '" & strBC & "') "
end if
end if

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-11 : 03:21:18
I think this is a vb6 generated code. If this is converted to SPPROC, you need to create a paramemter to access and pass value to your stored proc from your vb6 app. Just a thought!..:}

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page
   

- Advertisement -