0

I want to create a dynamic stored procedure that would do some simple checks and that is setting these variables

SET @valuesInput = (select count(distinct COLUMN_1) from [TABLE_1] where [COLUMN_1] is not null)
SET @valuesInserted = (select count(distinct COLUMN_2) from [TABLE_2])
SET @countDuplicates = (select count(*) from [TABLE_2] group by COLUMN_2 having count(*) > 1)
SET @error_message = 'FAILED: xxxxxxxx'

That I can call from other stored procedures that need these controls to be done.

What I need help for - is how I make it dynamic? I mean, I would call this stored procedure in another stored procedure which are using different table names and columns.

For example - I want to call this stored procedure in another stored procedure where TABLE_1: employee, COLUMN_1: name

Then in another stored procedure, where TABLE_1: orders, COLUMN_1: product.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sergio Tagliaferri
  • 23
  • 4
  • @GSerg, which part of that post could help me? – Sergio Tagliaferri Sep 23 '21 at 12:07
  • You will need to use dynamic sql - which that link demonstrates. But I suggest you don't do this at all. First read about [coupling and cohesion](https://www.geeksforgeeks.org/software-engineering-coupling-and-cohesion/). Don't encourage sloppy coding and careless design by putting these "checks" into many stored procedures. Your data should be correct when it is saved - not verified as correct everytime you need to access it. – SMor Sep 23 '21 at 12:11
  • FYI `where [COLUMN_1] is not null` is redundant in the first query; aggregate functions already ignore `NULL` values. – Thom A Sep 23 '21 at 12:33

0 Answers0