SQL Server stored procedure - reference two tables

sql server stored procedure return resultset
advanced stored procedure examples in sql server
sql server find table references in stored procedures
execute stored procedure sql server with input parameters
sql server stored procedure template
how to create stored procedure in sql server 2008 step by step
sql server stored procedure language

I am trying to create a select stored procedure where it takes a value from one table and sets this as a parameter for the second table.

As below I would like the user to input a reference number, which then gathers the registration ID from table 1, then performs a search in table two based on the registration ID, reference number and registration ID are both unique.

Reference number only exists in tableOne, registration_id exists in both

CREATE PROCEDURE SPSelect1
    @Reference_Number NVARCHAR(10),
    @Registration_ID NVARCHAR(10)
AS
    SET @Registration_ID = (tblOne.registration_id WHERE reference_number = @Reference_Number)

    SELECT * 
    FROM tblTwo 
    WHERE tblTwo.registration_id = @Registration_ID
END

The correct syntax is this:

SET @Registration_ID = (SELECT tblOne.registration_id where reference_number = @Reference_Number)

Or you could combine the two statements using a JOIN:

SELECT tblTwo.*
FROM   tblTwo
JOIN   tblOne ON tblTwo.registration_id = tblOne.registration_id
WHERE  tblOne.reference_number = @Reference_Number

SQL Server stored procedures for beginners, We will create a simple stored procedure that joins two tables and returns the result set as shown in the following example. Reference two tables - Stored Procedure? – Learn more on the SQLServerCentral forums SQL Server 2005; T-SQL (SS2K5) But I need to write a stored procedure that needs to reference a table

You could use:

select * 
from tblTwo 
where tblTwo.registration_id IN (SELECT tblOne.registration_id
                                 FROM tblOne
                                 WHERE reference_number = @Reference_Number)

sys.dm_sql_referenced_entities (Transact-SQL), A dependency between two entities is created when one For example, if a stored procedure is the specified referencing entity, this function such as tables, views, user-defined types (UDTs), or other stored procedures. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL Data Warehouse) Parallel Data Warehouse . A stored procedure in SQL Server is a group of one or more Transact-SQL statements or a reference to a Microsoft .NET Framework common runtime language (CLR) method.

i would like the user to input a reference number

Then your stored proc should only require a reference number as an input parameter. It can then query tblOne to find the registration ID corresponding to that reference number, and then query tblTwo using the registration ID in it's WHERE clause.

reference number and registration ID are both unique

Since you specified that the two fields are unique, I would use an INNER JOIN:

CREATE PROCEDURE SPSelect1
    @Reference_Number NVARCHAR(10)

AS

BEGIN 
    SELECT *
    FROM tblTwo
    INNER JOIN tblOne
    ON tblTwo.Registration_ID = tblOne.Registration_ID
    WHERE tblOne.Reference_Number = @Reference_Number                                        
END

GO

Stored Procedures (Database Engine), There are two types of table functions in SQL Server: inline and multi-statement functions. Inline Functions. Here is a example of an inline function adapted from A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure,

How to share data between stored procedures, Create stored procedure using inner join between two tables. REFERENCES [dbo]. If we put NOCOUNT OFF used in stored procedure, then after the execution of the stored procedure; the message tab in SQL Server will APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse. Creates a Transact-SQL or common language runtime (CLR) stored procedure in SQL Server, Azure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse.

Set Up a Database Diagram Using a Stored Procedure In SQL Server, of passing SQL Server data table as a parameter to stored procedures. However, with table-valued parameters, multiple rows can be To create the procedure, from the Query menu, click Execute. The procedure is created as an object in the database. To see the procedure listed in Object Explorer, right-click Stored Procedures and select Refresh. To run the procedure, in Object Explorer, right-click the stored procedure name HumanResources.uspGetEmployeesTest and select

Passing Data table as Parameter to Stored Procedures – {coding}Sight, Microsoft SQL Server and Sybase Adaptive Server achieve this with two For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired All the objects that reference this procedure must have references to this There is a system DMV that shows you all the tables and columns in your stored procedure: SELECT * FROM sys.dm_sql_referenced_entities ('yourSPSchema.YourSprocObjectName', 'OBJECT') improve this answer. edited Feb 2 '17 at 19:46. 38 silver badges. 49 bronze badges. answered Jul 28 '16 at 18:33. 11 bronze badges.