SQL Stored proc, select data from secondary tables

Question

I am trying to modify a stored procedure I wrote to pull data from other tables and insert it to a table.

Basically I have one table where we store mappings by ID. In that table I put together a field that should be storing the concatenation of that data, which works fine. The problem is it is storing the ID of the mapping, not the name that belongs to that ID.

My Stored Proc:

`USE [SNHULeads]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spSNHUCodeCreate]
(
@Source numeric(18,0),
@ProgramName numeric(18,0),
@Division numeric(18,0),
@Medium numeric(18,0),
@content varchar(50),
@URL varchar(500) = 'N/A',
@redirect varchar(100),
@Cost numeric(18,0),
@Notes varchar(500) = 'N/A',
@StartDate datetime,
@EndDate datetime,
@oper varchar(50),
@id varchar(50)
)

AS

DECLARE @SNHUCode numeric(18,0)
DECLARE @DateCreated datetime 
SET @SNHUCode = (SELECT MAX(snhuCODE) + 1 FROM [dbo].[VendorProgram])
SET @DateCreated = GETDATE()`

IF @URL != 'N/A'
BEGIN
    SET @URL = 'http://www.snhu.edu/' + @URL + '.asp?utm_source=' +    CONVERT(varchar(50), @Source) + '&utm_medium=' + CONVERT(varchar(50), @Medium) + '&utm_content=' + CONVERT(varchar(50), @content) + '&utm_campaign=' + CONVERT(varchar(50), @ProgramName) + '&SNHU_Segment=' + CONVERT(varchar(50), @Division)
END

INSERT INTO [SNHULeads].[dbo].[VendorProgram]
       ([vendorID]
       ,[programID]
       ,[divisionID]
       ,[mediumID]
       ,[snhuCODE]
       ,[content]
       ,[url]
       ,[cost]
       ,[Notes]
       ,[StartDate]
       ,[EndDate]
       ,[redirect]
       ,[DateCreated])
 VALUES
       (@Source
       ,@ProgramName
       ,@Division
       ,@Medium
       ,@SNHUCode
       ,@content
       ,@URL
       ,@Cost
       ,@Notes
       ,@StartDate
       ,@EndDate
       ,@redirect
       ,@DateCreated)`

Basically, with the URL column, I am catching the IDs I send in, which is not what I want. I cannot seem to find a way to select the name that corresponds to the @Source ID I send in.

I tried something like, declaring a new variable in the proc, and then selecting it like this:

DECLARE @sourceName varchar(50) SET @sName = (SELECT Leads.dbo.Vendors.Source WHERE mappingID = @Source)

That gives me an error, "The multi-part identifier "Vendors.mappingID" could not be bound.".

Any help out there for me? Don't bash the SQL too much, it's obviously not my strong point.

Answer 1

Ok I can't be sure this is what you want but assuming that @source relates to the column MappingId in a table called Vendors in a Database called Leads; then this SQL will set a variable called @sourceName to the corresponding Source Name. You can then Insert this data in to another table.

Is this what you were looking for?

    Declare @sourceName varchar(50)

    Select @sourceName = SourceName
    From Leads.dbo.Vendors
    Where MappingId = @Source

Note: This SQL assumes that only one row will exists for each @source passed in.