Desperately need tsql help

[rsutoratosu]

I'm not a programmer period. My boss decided to hand this to me.

Basically he wants sql sp to build a sql statement dynamically and output the date. from that statement you get an date output that plugs into another sql statement. I basically google and try to figure this out . Doesn't seem to work. Not sure what to try.

so there is a table sqlrule say
SQLDATE TABLENAME, ID1, ID2, ID3
DATE ; SQLTABLEA ; SOC; FUTURA; DR

SO basically my sql takes the 3 ID and find what to pick and from what table. Basically he would input a bunch of rules , ie SQLDATE will have different ID1-3 so if you use different ID1-3 parameter, you get a different SQLDATE
@SQLV = (Select 'SELECT ' +SQLDATE+ ' FROM ' + TABLENAME FROM SQLRULE where id1 = 'SOC' AND ID2 ='FUTURA', and ID3 = 'DR')
and upon exec (@SQLV)

it'll be 'SELECT DATE FROM SQLTABLEA' which will return ie 10/28/2020 (todays date)

So Im trying to run this in another store procedure but I'm not able to grab the output, I tried to set a @dateout output and use it within another sp and its just returning 1900/1/1 since i'm probably doing it wrong

so my second sp is like

CREATE PROCEDURE SP2 AS
.
.
Run the first date SP @dateout

SELECT 'xxx' as a, 'yyy' as b, 'zzz' as c, @dateout as DATE

Basically I have no idea what i'm doing.. so i'm probably not doing this right. If anyone have a crash course or something I can try .. thanks

 

[Juiblex]

Use an output parameter in the stored procedure like you mentioned.

Create or alter procedure storedproc2
(@parameterdate datetime output)
As
Begin
....
End

In the calling stored procedure you also need to use output. I'm guessing this is the part you are missing.

I'm not at my computer and on the phone so I can't give you an exact example but it would go something like:

Declare @returndate datetime
Exec storedproc2 @returndate = parameterdate output

Once you load it into the variable you can select it.

Give it a shot

 

[rsutoratosu]

I have something like that but ill try again. maybe i have parameters wrong..

 

[sdifox]

Why is it two stored proc at all? Just one stored proc would do, you just need to fetch the date first, store in variable, use in whatever you were going to do in what was going to go to second stored proc.

Hell you could inline the first query in the second query if it is not high impact.

 

[Cogman]

This isn't something you SHOULD do. Code to build SQL should live solely in the application.

To me, dynamically generated SQL is a MAJOR code smell that shouldn't be done lightly.

Just make 2 or more queries to the DB. The performance impact of a round trip isn't nearly as bad as the confusion that will be caused when someone tries to figure out WTH this stored proc is trying to accomplish.

Just because you CAN do it in the db, doesn't mean you should.

 

[rsutoratosu]

My boss has weird demands, plus im a fking accoutant, no one wants to work here, literally everyone has left and its him and me. Im leaving after dec pay check , but till then i at least need to do some work. Basically the recruiter goes he can't find anyone that wants to work there.

Basically he rip me a new one last night because he didn't design his program to look for dates with no data, it was import a date with 0 data because there was no data. and he got all upset that i didn't dynamically SQL this date function, everywhere.

Ok so what I have is
Name | TableName | Column
FIRST | IMPORT | DATE
SECOND | IMPORT2 | PREVIOUS_DATE

DECLARE @Var NVARCHAR(MAX)
DECLARE @SQLR NVARCHAR(MAX)
DECLARE @TABLENAME NVARCHAR(MAX)
SET @Var = (SELECT COLUMN FROM RULES WHERE name = 'FIRST')
SET @TABLENAME = 'TODAY'
SET @SQLR ='SELECT DISTINCT ' + @var+ ' FROM ' + @TABLENAME
EXEC (@sqlr)

So the execution would be which selects the date from table import
'SELECT DATE FROM TODAY'
or
SELECT PREVIOUS_DATE FROM TABLEXXX

so exec doesn't output the final date im getting, so i need to change it to exec sp_executesql xxxxx

 

[rsutoratosu]

So little back ground info, this guy makes weird demands, with less than 1 year trying to figure out SQL, i was pol sci / econ major.. basically accountant. he makes these weirds demands and says everything 'should be easy' which is not, he lives in 2008, while all our customer are on 2012-2019 and he's quoting me stuff from SQL 2008 that doesn't work today, ie SSISDB fisco.

as project comes to a close, he will make radical changes, every month im living through this. Some changes are easy, some will take major rewrite of code and tables. The people getting screw are the customer with bigger bill due to his last minute redesign.

last night he kept me at work for 3 extra hours doing redesign.. so something that was completed gets like 10 table changes, and his famous words ' should be easy '

This is one of many reasons why Im leaving at end of this month, during covid when our state and office was shut down, he would not pay me if i did not come in to work. then he paid me half claiming that the customers were paying late, then he said it was because i didn't come in, 2 month later. So the entire building is close, i was the only one in the office working. Everyone else work at home. Nothing was open, i couldn't get food, they shut the kitchen down to prevent spreading virus, etc, etc.. no fridges, etc.. basically horrible person to work for

 

[sdifox]

lol [redacted] that guy

LOL, watch your [redacted] language! This ain't no [redacted] social forum! -- [redacted] Programming Moderator Ken g6

 

[sdifox]

Sorry

 

[Cogman]

I agree with sdifox's unredacted opinion

Are you hourly? Document everything and the work you did. Write it down so that when at the end of your employment he tries to stiff you (Which, he absolutely will do), you can report him to your department of labor and get a pretty penny.

Also, if you can, I'd suggest getting written documentation about him paying you half and saving off those docs. You'll want to contact a labor lawyer, because, screw that guy. You aren't allowed to just pay someone half without their written permission. It doesn't matter if "customers aren't paying".

Deductions from your paycheck

Learn when your employer is allowed to take money out of your pay.

www.masslegalhelp.org

 

[rsutoratosu]

Thanks for all the help, lets just close the issues about work and leave it as sql code. Sorry I bought it up but thats the reasonsing behind all these weird coding request. I appreciate the help here and even though I wont be programing much longer. Super appreciate all the help, helps me learn something I might not even do again.