Using stored procedures/dynamic sql to add columns (SOLVED)

Question

Post reply

Using stored procedures/dynamic sql to add columns (SOLVED)

AWOODS8005

SSC Veteran

Points: 268
More actions
December 4, 2019 at 6:42 pm

#3704450
I am trying to use stored procedures to join columns to one table. I am joining more than 1 table to the original table but they have a common identifier. For example, right now I have
Table 1
Id_number month
82284 jan
82284 feb
29292 jan

table 2
idnumber month weight
82284 jan 185

table 3
idnumber month age
82284 jan 35
29292 jan 48

I am trying to join table 2 and table 3 to table 1 but rename the columns as Indicator1 and indicator2. The plan is to have more tables and more indicators later on. The final table will hopefully look like this

idnumber month IND1 IN2
82284 jan 185 35
82284 feb null null
29292 jan null 48

I am hoping to combine these in a stored procedure so that neither table is specific to Indicator 1 or 2 and I can add even more than just two indicators and create a combined table that shows all values.
So far I just have stored procedures that only join 1 table at a time.
I will post my code I have so far below.
I have been using SQL server for a while but am very new to stored procedures so any help is appreciated.

****My version is Microsoft SQL server management studio 2014.
```
ALTER PROCEDURE
DBO.AGETABLE
AS
BEGIN
DECLARE @COLUMN INT
SELECT 
A.*
, B.AGE
FROM TABLE1 A
LEFT JOIN TABLE3 B
ON IDNUMBER
END

ALTER PROCEDURE
DBO.WEIGHTTABLE
AS
BEGIN
DECLARE @COLUMN INT
SELECT 
A.*
, B.WEIGHT
FROM TABLE1 A
LEFT JOIN TABLE2 B
ON IDNUMBER
END


EXEC DBO.WEIGHTTABLE
EXEC DBO.AGETABLE
```
- This topic was modified 4 years, 11 months ago by AWOODS8005.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

heb1014 Hall of Fame Points: 3790 More actions · Answer 2

Hello. I'm not sure if you are still looking for feedback on this, but I have a thought. First, whenever possible, I think it's good to step back and look at design. Why do we have different indicators in different tables? It would probably be better to have a design that is extensible without having to create more tables and use dynamic SQL. You may be able to craft a beautiful procedure to use dynamic SQL for your current need, but what about the future? Do you continue using dynamic SQL for future needs?

I have additional thoughts if you're interested.

AWOODS8005 SSC Veteran Points: 268 More actions · Answer 3

I figured it out thank you. This was a very early step in what turned out to be a long project.

I ended up using two indicators with a large IF statement. Thanks for the help though.