September 5, 2002 at 6:09 pm
In VB it is possible to build a SQL statement based on criteria. But this is largely because SQL statements are strings. These strings are then passed to SQL Server for execution.
I would like to be able to do something similar with SQL Server stored procedures. Unfortunately, when I look at the SQL in a stored procedure, the SQL statement is not a string. So it doesn't look possible to do this in a stored procedure. Does anyone know how to build SQL statements within a stored procedure?
September 5, 2002 at 7:41 pm
You can do it with Exec(). Downsides are you dont get a compiled query plan, security is evaluated at run time, presents a potential security flaw via an injection attack. Still, at times it makes sense.
Andy
September 5, 2002 at 9:36 pm
Thanks Andy.
It's too bad! That's one less reason to use stored procedures - at least for single record inserts.
It looks like the code will have to be a method of a table class in VB instead. Would you agree with this? Or is there a better option?
Thanks,
Mike
September 6, 2002 at 4:06 am
I don't actually agree with putting the insert in the application code. At least not if the only reason for it is the lack of a stored query plan.
If you would build and execute the query from your app, you won't be able to gain any performance profit either. The query you are executing there won't have any stored execution plan either.
September 6, 2002 at 5:27 am
You can still use SP's the key is create a security layer for the incoming code.
Ex.
CREATE PROC sp_DynamSQL
@keywords VARCHAR(8000),
@col INT
AS
SET NOCOUNT ON
if CHARINDEX('TRUNCATE', @keywords) > 0 OR
CHARINDEX('DROP', @keywords) > 0 OR
CHARINDEX('CREATE', @keywords) > 0 OR
CHARINDEX('DELETE', @keywords) > 0 OR
CHARINDEX('SELECT', @keywords) > 0 OR
CHARINDEX('UPDATE', @keywords) > 0 OR
CHARINDEX('EXEC', @keywords) > 0 OR
CHARINDEX('sp_', @keywords) > 0 /*OR
anything else that could potentially compromise your databases*/
BEGIN
/*Log details of connection here*/
RASIERROR('I'm sorry but you can bug off.' 16, -1)
RETURN
END
By looking for specific potential problems you can effectively stop the injection attack.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 6, 2002 at 7:33 am
Also it depends on how you plan to build dynamic sql in your sp. If the user is not submitting the sql to the sp, but your sp generates dynamic sql based on data in a SQL table, then there may be no need for the additional security layer.
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 6, 2002 at 9:44 am
Hi,
strINSERT = "INSERT INTO tblPerson ( "
strSELECT = " SELECT "
'Gender
If cboGender <> "" Then
strINSERT = strINSERT & "Gender"
strSELECT = strSELECT & QUOTES & cboGender & QUOTES
End If
'First Name
If txtFirstName <> "" Then
strINSERT = strINSERT & ", FirstName"
strSELECT = strSELECT & ", " & QUOTES & txtFirstName & QUOTES
End If
'Last Name
If txtLastName <> "" Then
strINSERT = strINSERT & ", LastName"
strSELECT = strSELECT & ", " & QUOTES & Me!txtLastName & QUOTES
End If
'Last part of the "INSERT" piece
strSQL = strINSERT & strSELECT
The above VB code is an example of what I am doing in the front end. The reason I am doing it this way is because some fields in the table are not required, so it is not necessary to force the user to provide a value. In an INSERT SELECT statement, if you designate a field, you must also provide a value; otherwise you get an error. However, it occurred to me that I might do the same thing in a stored procedure. I would have a parameter for all the fields and only include a field in the INSERT SELECT statement if the parameter was not null.
However, when I looked at sample stored procedures, I noticed that the SQL statements were not strings and therefore I might not be able to do this kind of thing there. Perhaps I should do it in the middle tier. This is what I was thinking.
Is there another way to do this?
Mike
September 6, 2002 at 10:44 am
Actually in Procs you can set default values and work with them.
Ex.
CREATE PROC sp_InData
@FirstName = NULL, Default value of NULL
@LastName --No default is required.
AS
INSERT INTO
tblUser (FName, LName)
VALUES (@FirstName, @LastName)
SELECT * FROM tblUser WHERE
(CASE WHEN @FirstName IS NULL THEN '' ELSE FName END = CASE WHEN @FirstName IS NULL THEN '' ELSE @FirstName END ) AND LName = @LastName
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 6, 2002 at 11:14 am
Is this what you are looking for?
CREATE PROCEDURE BuildDynamic
(
@cboGender char(1),
@txtFirstName varchar(10) = '',
@txtLastName varchar(10) = ''
) AS
DECLARE
@strSQL varchar(2000),
@strINSERT varchar(1000),
@strSELECT varchar(1000)
SET @strINSERT = 'INSERT INTO tblPerson ('
SET @strSELECT = + CHAR(10) + 'SELECT '
--Gender
IF @cboGender <> ''
BEGIN
SET @strINSERT = @strINSERT + 'Gender'
SET @strSELECT = @strSELECT + '''' + @cboGender + ''''
END
--First Name
IF @txtFirstName <> ''
BEGIN
SET @strINSERT = @strINSERT + ', FirstName'
SET @strSELECT = @strSELECT + ', ''' + @txtFirstName + ''''
END
--Last Name
IF @txtLastName <> ''
BEGIN
SET @strINSERT = @strINSERT + ', LastName'
SET @strSELECT = @strSELECT + ', ''' + @txtLastName + ''''
END
--Last part of the "INSERT" piece
SET @strSQL = @strINSERT + ')' + @strSELECT
PRINT @strSQL
EXEC (@strSQL)
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
September 6, 2002 at 1:31 pm
Hi,
I will have to try the last two suggestions and then let everyone know how they worked out.
Thanks,
Mike
September 8, 2002 at 6:44 pm
Hey Mike,
is this table being used a lot? If so any reason why you're not creating and maintaining an ADO recordset and doing a .addnew and a .update.
Cheers,
mike
September 8, 2002 at 9:00 pm
use sp_executeSql - if you fully qualify the dynamic sql in the SP SQL Server will precompile and bind if you rexecute the same statement multiple times...
--woody
C. Woody Butler, MCP
C. Woody Butler, MCP
cwbutler@cwoodybutler.com
September 9, 2002 at 6:54 am
Be -very- careful about the "string-building" you do. Your first/last name strings [should] be OK as is, but if you give enough elbow room in the length of a variable, a potential first name string could be:
' truncate table tblPerson
which would, in effect, build your SQL string to look like:
INSERT INTO tblPerson (Gender, Firstname)
SELECT 'M', ''
TRUNCATE TABLE tblPerson
Think about it.
I like Antares686's check, though. Good idea.
Cheers,
Ken
September 9, 2002 at 9:06 am
To avoid the issue of people executing their own code you can use this code for each variable that is non numeric:
SET @txtLastName = REPLACE(@txtLastName,'''','''''')
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
September 12, 2002 at 1:10 am
Hi folks,
I want to thank you all for your input on this subject. My only objective in this case was to create an INSERT stored procedure that would handle the situations in which a field could hold a NULL and the parameters passed for them were NULL. Antares' suggestion of putting in NULL defaults for unrequired fields sounded very simple and straight forward; so I gave it a try. I worked very well.
Thanks again,
Mike
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply