Trying to build a SQL query by passing a string parameter to an sproc

Posted on 2012-09-13
Last Modified: 2012-09-19
Hi I have the following procedure where I pass in a string for part of the SELECT statement
An example of the @orParams string is:

"ID = 1 OR ID = 3 OR ID = 9"

However, my syntax must be wrong because I'm getting the error:
Error: System.Data.SqlClient.SqlError: Incorrect syntax near '='.

Please can someone tell me what's wrong with my sproc below (never done this before ):

PROCEDURE dbo.spSkillsLookUP
/* Input Parameters */
(@orParams nvarchar(4000))
    Set NoCount ON
    /* Variable Declaration */
    Declare @SQLQuery AS NVarchar(4000)

/* Build the Transact-SQL String with the input parameters */
set @SQLQuery = 'SELECT Prof_Desc FROM tblSkills WHERE' + @orParams

Execute sp_Executesql @SQLQuery
Question by:Lorna70
    LVL 25

    Accepted Solution

    Change this:

    set @SQLQuery = 'SELECT Prof_Desc FROM tblSkills WHERE' + @orParams


    set @SQLQuery = 'SELECT Prof_Desc FROM tblSkills WHERE ' + @orParams
    LVL 32

    Assisted Solution

    You need a space between where and your params

    set @SQLQuery = 'SELECT Prof_Desc FROM tblSkills WHERE ' + @orParams

    or to be sure

    set @SQLQuery = 'SELECT Prof_Desc FROM tblSkills WHERE' + '  ' + @orParams

    Author Comment

    D'oh!  Thank goodness for you guys - you keep me sane ha ha!

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now