Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: Tsql command parameter

    Hi all,

    I am having a problem with a select query when I type it in the Visual Express Query Builder:

    select ....
    where idNumber = @id

    It works fine if the idNumber is say 's222' as idNumber is declared as nvarchar(MAX) but it is not working with the ID = l271282380464c. The error was:
    unknown column l271282380464c

    if I put the ID directly in the query like this

    select ....
    where idNumber LIKE 'l271282380464c'

    this works but I want to add a parameter so that it is dynamic. How do I construct the TSql
    Any help on this? Thanks.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this
    Code:
    create table DaTable (
    	idNumber	nvarchar(MAX)	not null
    )
    
    INSERT INTO DaTable VALUES ('s222')
    INSERT INTO DaTable VALUES ('l271282380464c')
    
    DECLARE @id nvarchar(MAX)
    
    SET @id = 's222'
    
    select *
    FROM DaTable
    where idNumber = @id
    
    SET @id = 'l271282380464c'
    
    select *
    FROM DaTable
    where idNumber = @id
    
    SET @id = 'blabla'
    
    select *
    FROM DaTable
    where idNumber = @id
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    Hi Wim. This is the output:

    Running [dbo].[test1].

    idNumber
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    s222
    (3 row(s) affected)
    (1 row(s) returned)
    idNumber
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    l271282380464c
    (3 row(s) affected)
    (1 row(s) returned)
    idNumber
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    (3 row(s) affected)
    (0 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[test1].

  4. #4
    Join Date
    Jul 2009
    Posts
    168
    But you see I am not using the stored procedure in this case because am using the VSWD 2008. So I am inserting the sql command directly in the query builder and this is where the problem is coming. I think it's the problem with the quotation marks:

    where idNumber LIKE 'l271282380464c' works
    my whole sql is like this:

    Code:
    SELECT DISTINCT 
                             PaymentPlan.installments, COALESCE (parent.name + ' (', '') + child.name + CASE WHEN parent.name IS NULL THEN '' ELSE ')' END AS course, 
                             CourseFees.fees, PaymentMethod.name AS PaymentMethod, PaymentPlan.name, Student.studentID, StudentPayment.courseFeesID, 
                             CourseFees.paymentPlanID, CourseFees.courseID
    FROM            PaymentPlan INNER JOIN
                             CourseFees ON PaymentPlan.paymentPlanID = CourseFees.paymentPlanID INNER JOIN
                             Courses AS child ON child.courseID = CourseFees.courseID INNER JOIN
                             StudentPayment ON CourseFees.courseFeesID = StudentPayment.courseFeesID INNER JOIN
                             Student ON StudentPayment.studentID = Student.studentID INNER JOIN
                             PaymentMethod ON StudentPayment.paymentMethodID = PaymentMethod.paymentMethodID LEFT OUTER JOIN
                             Courses AS parent ON parent.courseID = child.parentID
    WHERE        (Student.idNumber = @id)
    So how do I insert the quotation mark so that it results as the above working query? Thanks

  5. #5
    Join Date
    Jul 2009
    Posts
    168
    Ok sorry about this guys, the problem was not coming from the tsql but rather from a mistake while inputting some values in a table. One required value was missing and was causing the sql statement to crash...thanks again wim for your kind help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •