Subquery within Parametized SQL

Question

I am using the following code to add some paramitized values to an SQL table.

'--Connect to datasource
Dim SqlconnectionString As String = "server=inlt01\SQLEXPRESS; database=DaisyServices; integrated security=yes"

'--Import selected file to Billing table and Master Services
Dim strSql As String = "INSERT INTO [" + FileNameOnly + "] (Site,CLI,FromDate,ToDate,Quantity,UnitCost,TotalCost,[Description],[User],Department,Filenameonly,billingmonth) VALUES (@Site,@CLI,@FromDate,@ToDate,@Quantity,@UnitCost,@TotalCost,@Description,@User,@Department,@filenameonly,(SELECT  ( CASE SUBSTRING(@filenameonly,1,3)WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6 WHEN 'Jul' THEN 7 WHEN 'Aug' THEN 8 WHEN 'Sep' THEN 9 WHEN 'Oct' THEN 10 WHEN 'Nov' THEN 11 WHEN 'Dec' THEN 12 END ))); INSERT INTO [DaisyServicesMaster] (Site,CLI,FromDate,ToDate,Quantity,UnitCost,TotalCost,[Description],[User],Department,filenameonly,billingmonth) VALUES (@Site,@CLI,@FromDate,@ToDate,@Quantity,@UnitCost,@TotalCost,@Description,@User,@Department,@filenameonly,(SELECT  ( CASE SUBSTRING(@filenameonly,1,3)WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6 WHEN 'Jul' THEN 7 WHEN 'Aug' THEN 8 WHEN 'Sep' THEN 9 WHEN 'Oct' THEN 10 WHEN 'Nov' THEN 11 WHEN 'Dec' THEN 12 END )))"

        Using connection As New SqlClient.SqlConnection(SqlconnectionString)

        Dim cmd As New SqlClient.SqlCommand(strSql, connection) ' create command objects and add parameters
        With cmd.Parameters
                  .Add("@Site", SqlDbType.VarChar, 30, "Site")
                  .Add("@CLI", SqlDbType.VarChar, 30, "CLI")
                  .Add("@FromDate", SqlDbType.Date, 30, "FromDate")
                  .Add("@ToDate", SqlDbType.Date, 30, "ToDate")
                  .Add("@Quantity", SqlDbType.Int, 3, "Quantity")
                  .Add("@UnitCost", SqlDbType.Float, 5, "UnitCost")
                  .Add("@TotalCost", SqlDbType.Float, 5, "TotalCost")
                  .Add("@Description", SqlDbType.VarChar, 100, "Description")
                  .Add("@User", SqlDbType.VarChar, 30, "User")
                  .Add("@Department", SqlDbType.VarChar, 30, "Department")
                  .AddWithValue("@filenameonly", FileNameOnly)

         End With

For the @CLI value I would like to use a sub query to truncate the first character of the string

SELECT RIGHT(CLI, LEN(CLI) - 1)

How to I incorporate a sub query into my Paramitized SQL?

I am relatively new to VB coding so if you could provide some example code that would be much appreciated.

do that in vb and pass it as an additional parameter to your sql. since you are using embedded sql there is no reason to do that in sql instead of vb. use vb: it's easier, quicker, cleaner. — Paolo
– Paolo, Commented Dec 19, 2014 at 10:56

SQLDiver · Accepted Answer · 2014-12-19 11:13:43Z

It is not possible to pass code as a parameter. The purpose of parametarized queries is to prevent code being passed via a parameter in order to protect against SQL injection attacks.

There are two ways to achieve your desired outcome:

Truncate the value before passing as a parameter
Truncate the value after the parameter has been passed.

The latter would mean changing your sql code to the following:

Dim strSql As String = "INSERT INTO [" + FileNameOnly + "] (Site,CLI,FromDate,ToDate,Quantity,UnitCost,TotalCost,[Description],[User],Department,Filenameonly,billingmonth) VALUES (@Site,RIGHT(@CLI, LEN(@CLI) - 1),@FromDate,@ToDate,@Quantity,@UnitCost,@TotalCost,@Description,@User,@Department,@filenameonly,(SELECT  ( CASE SUBSTRING(@filenameonly,1,3)WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6 WHEN 'Jul' THEN 7 WHEN 'Aug' THEN 8 WHEN 'Sep' THEN 9 WHEN 'Oct' THEN 10 WHEN 'Nov' THEN 11 WHEN 'Dec' THEN 12 END ))); INSERT INTO [DaisyServicesMaster] (Site,CLI,FromDate,ToDate,Quantity,UnitCost,TotalCost,[Description],[User],Department,filenameonly,billingmonth) VALUES (@Site,RIGHT(@CLI, LEN(@CLI) - 1),@FromDate,@ToDate,@Quantity,@UnitCost,@TotalCost,@Description,@User,@Department,@filenameonly,(SELECT  ( CASE SUBSTRING(@filenameonly,1,3)WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6 WHEN 'Jul' THEN 7 WHEN 'Aug' THEN 8 WHEN 'Sep' THEN 9 WHEN 'Oct' THEN 10 WHEN 'Nov' THEN 11 WHEN 'Dec' THEN 12 END )))"

One option you might consider is instead of using parameterized queries, to put the code into a stored procedure and invoke this stored procedure with parameters. It is common practice in a database application to have a set of CRUD stored procedures - Create, Read, Update, Delete. There are even scripts out there to generate base templates from the structure of your table. Here is one example:

http://www.sqlbook.com/SQL-Server/Auto-generate-CRUD-Stored-Procedures-40.aspx

Collectives™ on Stack Overflow

Subquery within Parametized SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related