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.
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. |
|
|
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* |
|
|
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
|
|
|
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 |
|
|
|
|
|
|
|