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 2008 Forums
 Transact-SQL (2008)
 Converting Dynamic to SPROC
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

105 Posts

Posted - 02/07/2013 :  15:21:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4608 Posts

Posted - 02/07/2013 :  16:00:14  Show Profile  Reply with Quote
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.

Edited by - Lamprey on 02/07/2013 16:05:54
Go to Top of Page

tech_1
Posting Yak Master

105 Posts

Posted - 02/07/2013 :  16:16:31  Show Profile  Reply with Quote
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

105 Posts

Posted - 02/10/2013 :  21:10:33  Show Profile  Reply with Quote
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 - 02/11/2013 :  03:21:18  Show Profile  Reply with Quote
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

Edited by - adbasanta on 02/11/2013 03:22:03
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.05 seconds. Powered By: Snitz Forums 2000