March 13, 2018 at 6:40 am
Hi All,
i have a table in database
Code signfrompack frompack signtopack topack
12345 > 0 <= 5
12346 > 0 < 5
123457 > 5 < 15
signfrompack and signtopack is columns with sign values in table.
i need to create procedure with parameter @pack and return all records in range
example:
create procedure test
@pack decimal(9,4)
as
declare @sql nvarchar(max)
set @sql='select code from table
where '+str(@pack,9,4)+'signfrompack frompack and '+str(@pack,9,4)+'signtopack topack'
sp_executeSql @sql
GO
exec procedure test 5
and to return only first rows.
how can i pass signfrompack and signtopack as sign values from table
thanks
March 13, 2018 at 7:23 am
roman.goldenberg - Tuesday, March 13, 2018 6:40 AMHi All,
i have a table in database
Code signfrompack frompack signtopack topack12345 > 0 <= 5
12346 > 0 < 5
123457 > 5 < 15
signfrompack and signtopack is columns with sign values in table.
i need to create procedure with parameter @pack and return all records in range
example:
create procedure test
@pack decimal(9,4)
as
declare @sql nvarchar(max)
set @sql='select code from table
where '+str(@pack,9,4)+'signfrompack frompack and '+str(@pack,9,4)+'signtopack topack'sp_executeSql @sql
GOexec procedure test 5
and to return only first rows.
how can i pass signfrompack and signtopack as sign values from tablethanks
This doesn't much sense at all. Not a big fan of storing quasi formulas like this which require dynamic sql. There are usually better ways of designing the tables so this can be avoided. But I don't understand at all what you are trying to do here. This blog post would be a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2018 at 9:07 am
I think I know what the poster is looking for, and I agree that the concept is problematic, as using this code has some potentially really bad performance characteristics, this will function for small data quantities:/*i have a table in database*/
CREATE TABLE #TABLE (
Code int,
signfrompack varchar(2),
frompack int,
signtopack varchar(2),
topack int
);
INSERT INTO #TABLE (Code, signfrompack, frompack, signtopack, topack)
VALUES (12345, '>', 0, '<=', 5),
(12346, '>', 0, '<', 5),
(123457, '>', 5, '<', 15 );
GO
CREATE PROCEDURE dbo.TEST (
@pack decimal(9,4)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT T.Code
FROM #TABLE AS T
WHERE
CASE signfrompack
WHEN '>' THEN IIF(@pack > frompack, 1, 0)
WHEN '>=' THEN IIF(@pack >= frompack, 1, 0)
END = 1
AND
CASE signtopack
WHEN '<' THEN IIF(@pack < topack, 1, 0)
WHEN '<=' THEN IIF(@pack <= topack, 1, 0)
END = 1;
END;
GO
EXEC dbo.TEST @pack = 5;
GO
DROP TABLE #TABLE;
GO
Some caveats:
1.) I assume that each range is always going to be an inclusion range, as opposed to an exclusion range. This means that the first sign will never be > or >=, and the second one will always be < or <=.
2.) The quantity of data and conditions is relatively small.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 13, 2018 at 11:25 pm
Hi Steve,
That's exactly what I need.
thanks
March 14, 2018 at 12:18 am
Sean Lange - Tuesday, March 13, 2018 7:23 AMroman.goldenberg - Tuesday, March 13, 2018 6:40 AMHi All,
i have a table in database
Code signfrompack frompack signtopack topack12345 > 0 <= 5
12346 > 0 < 5
123457 > 5 < 15
signfrompack and signtopack is columns with sign values in table.
i need to create procedure with parameter @pack and return all records in range
example:
create procedure test
@pack decimal(9,4)
as
declare @sql nvarchar(max)
set @sql='select code from table
where '+str(@pack,9,4)+'signfrompack frompack and '+str(@pack,9,4)+'signtopack topack'sp_executeSql @sql
GOexec procedure test 5
and to return only first rows.
how can i pass signfrompack and signtopack as sign values from tablethanks
This doesn't much sense at all. Not a big fan of storing quasi formulas like this which require dynamic sql. There are usually better ways of designing the tables so this can be avoided. But I don't understand at all what you are trying to do here. This blog post would be a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
sorry,
it was my first post.
March 14, 2018 at 5:59 am
roman.goldenberg - Tuesday, March 13, 2018 11:25 PMHi Steve,
That's exactly what I need.thanks
Glad I could help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 14, 2018 at 7:23 am
roman.goldenberg - Wednesday, March 14, 2018 12:18 AMSean Lange - Tuesday, March 13, 2018 7:23 AMroman.goldenberg - Tuesday, March 13, 2018 6:40 AMHi All,
i have a table in database
Code signfrompack frompack signtopack topack12345 > 0 <= 5
12346 > 0 < 5
123457 > 5 < 15
signfrompack and signtopack is columns with sign values in table.
i need to create procedure with parameter @pack and return all records in range
example:
create procedure test
@pack decimal(9,4)
as
declare @sql nvarchar(max)
set @sql='select code from table
where '+str(@pack,9,4)+'signfrompack frompack and '+str(@pack,9,4)+'signtopack topack'sp_executeSql @sql
GOexec procedure test 5
and to return only first rows.
how can i pass signfrompack and signtopack as sign values from tablethanks
This doesn't much sense at all. Not a big fan of storing quasi formulas like this which require dynamic sql. There are usually better ways of designing the tables so this can be avoided. But I don't understand at all what you are trying to do here. This blog post would be a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
sorry,
it was my first post.
No problem. Steve was able to understand what you needed. Glad you got it sorted out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply