Using Select statement as parameter for SPROC

Posted on 2006-05-09
Last Modified: 2012-06-27
I have an sproc that has parameter A(varchar(30) and parameter B(binary(8).

parameter B is a record ID in my database

If I execute
rsp_getnewidwithretval 'Distribution',0x0000000000002ED1

It works, BUT I'm trying to dynamically assign parameter B. I've tried many variations including:
exec rsp_getnewidwithretval 'Distribution',(Select Top 1 Distribution_ID from Distribution)
Select Top 1 Distribution_ID from Distribution
exec productioned.dbo.rsp_getnewidwithretval 'Distribution',Distribution_Id

but end up getting:
Incorrect syntax near '('.
Implicit conversion from data type nvarchar to binary is not allowed. Use the CONVERT function to run this query.

Question by:pauldes
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    > exec productioned.dbo.rsp_getnewidwithretval 'Distribution',Distribution_Id

    the sysntax for exec is

    exec ('ur dynamic code')  
    LVL 142

    Accepted Solution

    declare @b varbinary(50)
    select top 1 @b = cast(Distribution_ID as varbinary(50)) from Distribution
    exec rsp_getnewidwithretval 'Distribution', @b
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    Sorry, leave my post, Angel is right

    Author Comment


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now