locked
Int Parameter RRS feed

  • Question

  • User-797751191 posted

    Hi

      To the below line i want to add Int parameter to qry variable . Secondly any better way to write below line

    set @qry = 'select * from test1 where [Date] between '''+cast(@FromDate as varchar)+''' and '''+cast(@ToDate as varchar)+''''

    Thanks

    Monday, July 8, 2019 1:15 PM

Answers

  • User753101303 posted

    Stricly speaking the issue is that you are adding a string and a numeric value in which case SQL Server tries to convert the string to a numeric value. So here you would need to use :

    set @qry = @qry + ' and [Status] = ' + CAST(@Status AS INT)

    You can still use parameters on the server side using something such as :

    DECLARE @sql NVARCHAR(max)
    SET @sql='SELECT * FROM sys.databases WHERE database_id=@dbid OR @dbid IS NULL'
    EXEC sp_executesql @sql,N'@dbid INT',1 -- master database only 
    EXEC sp_executesql @sql,N'@dbid INT',NULL -- all databases

    but then you could run directly the same statement without going through sp_executesql. For now I really wonder if you are not doing all this in a way which is more complex than needed...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 8, 2019 4:00 PM

All replies

  • User475983607 posted

    jsshivalik

      To the below line i want to add Int parameter to qry variable . Secondly any better way to write below line

    set @qry = 'select * from test1 where [Date] between '''+cast(@FromDate as varchar)+''' and '''+cast(@ToDate as varchar)+''''

    Use a parameter query like you are doing in your other threads!!! 

    Also explain the problem you are trying to solve, include expected results, and actual results.

    Monday, July 8, 2019 1:19 PM
  • User753101303 posted

    Hi,

    I would just use :

    set @qry = 'select * from test1 where [Date] between @FromDate and @ToDate'

    You'll then use sp_executesql ? Not sure why you cast those values to varchar ?

    Monday, July 8, 2019 1:39 PM
  • User-797751191 posted

    Hi

      I am getting this error . Status field in Database is Int

    alert('Conversion failed when converting the varchar value 'select * from [test1] where [Date] between '2019-07-01' and '2019-07-08' and [Status] = ' to data type int.');</script>

    @FromDate date,
    @ToDate date,
    @Status int = Null

    cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime("2019-07-01"));
    cmd.Parameters.AddWithValue("@ToDate", DateTime.Now);
    cmd.Parameters.AddWithValue("@Status", Convert.ToInt32(ddlStatus.SelectedItem.Value));

    set @qry = 'select * from [Test1] where [Date] between '''+cast(@FromDate as varchar)+''' and '''+cast(@ToDate as varchar)+''''
    set @qry = @qry + ' and [Status] = ' + @Status

    Secondly when i write below line it does not return any data

    set @qry = 'select * from [Test1] where [Date] between @FromDate and @ToDate'

    Thanks

    Monday, July 8, 2019 1:45 PM
  • User753101303 posted

    You showing both C# and Transact SQL code ???

    I would expect just C# code :
    qry = "select * from [Test1] where [Date] between @FromDate  and  @ToDate and Status=@Status";
    cmd=new SqlCommand(qry);
    cmd.Parameters.AddWithValue etc...
    // not sure if uisng a DataReader ?

    For now it seems you try to assign server side this sql statement string to an integer variable ?

    Monday, July 8, 2019 1:57 PM
  • User-797751191 posted

    Hi PatriceSc . @FromDate,ToDate,@Status are parameters

    @FromDate date,
    @ToDate date,
    @Status int = Null
    
    set @qry = 'select * from [Test1] where [Date] between '''+cast(@FromDate as varchar)+''' and '''+cast(@ToDate as varchar)+''''
    set @qry = @qry + ' and [Status] = ' + @Status
    
    
    
    Below is C# Code
    cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime("2019-07-01"));
    cmd.Parameters.AddWithValue("@ToDate", DateTime.Now);
    cmd.Parameters.AddWithValue("@Status", Convert.ToInt32(ddlStatus.SelectedItem.Value));

    Thanks

    Monday, July 8, 2019 2:19 PM
  • User753101303 posted

    I first thought you were trying to build a parameterized query on the SQL Server side but it seems now some kind of misunderstanding about how parameters are working. They are handled by SQL Server out of the box and your SP could be just:

    CREATE PROCEDURE Test
    @FromDate date,
    @ToDate date,
    @Status int = Null
    AS select * from [Test1] where [Date] between @FromDate and @ToDate and [Status] = @Status
    GO

    You don't have to build a string yourself a string that includes those parameters as literal values and then execute this string.

    Edit and then  you can run this SP using for example

    EXEC Test '20190708','20190709',2

    SQL Server knows out of the box how to run a query with "variables" rather than with literal values. You don't have to build and run a string that contains only literal values.

    Monday, July 8, 2019 2:43 PM
  • User-797751191 posted

    Hi PatriceSc

      Thanks , I need to build a string. There are other parametrs also . If there is any value then they should be added to string else not

    Thanks

    Monday, July 8, 2019 3:42 PM
  • User753101303 posted

    Stricly speaking the issue is that you are adding a string and a numeric value in which case SQL Server tries to convert the string to a numeric value. So here you would need to use :

    set @qry = @qry + ' and [Status] = ' + CAST(@Status AS INT)

    You can still use parameters on the server side using something such as :

    DECLARE @sql NVARCHAR(max)
    SET @sql='SELECT * FROM sys.databases WHERE database_id=@dbid OR @dbid IS NULL'
    EXEC sp_executesql @sql,N'@dbid INT',1 -- master database only 
    EXEC sp_executesql @sql,N'@dbid INT',NULL -- all databases

    but then you could run directly the same statement without going through sp_executesql. For now I really wonder if you are not doing all this in a way which is more complex than needed...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 8, 2019 4:00 PM