Sql Server issue on stored procedure

roy_sanu
roy_sanu used Ask the Experts™
on
LookoneStoredProcedure.txtI have a stored procedure got it from internet and i would like implement similar type in my work
can you all help me on it as this stored procedure does not looks good to me.


SET @offsetIndex = @pageNumber * @numberOfItemsPerPage;

SET @outerQuery =
    'WITH outer_query AS (SELECT  inner_q.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as hbn_row_no FROM
 (SELECT a.c_action_id AS actionId, a.c_action_type_id AS actionType,
 a.c_isactive as isactive, (SELECT l.c_valuez FROM t_lookup_actions_lang  l
 WHERE a.c_action_id=l.c_action_id AND l.c_lang_id=1) as english,
 (SELECT l.c_valuez FROM t_lookup_actions_lang l WHERE a.c_action_id=l.c_action_id AND l.c_lang_id=2)
 as arabic  FROM t_lookup_actions a ) inner_q )  
 SELECT actionId, actionType, isactive, english, arabic FROM outer_query '+ @searchQuery +' ORDER BY ' + @sortFields +'  OFFSET ' + CONVERT(VARCHAR(12), @offsetIndex) + ' ROWS FETCH NEXT ' + CONVERT(VARCHAR(12), @numberOfItemsPerPage) + ' ROWS ONLY ';


Right now not able to understand the sql query it works, can you some of the expert on sql server help me on it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
This looks like a common table expressions
The variable @outerquery is getting assign an SQL .......

That is once @outerquery is run, a table outerquery with data will be available
;with tablename as ( select)
Defines a table tablename  populated with data returned from the select query in memory, for the session.

It is commonly better to describe what you want your storedprocedure to do and if you think you found something and would like to confirm ....
roy_sanudeveloper

Author

Commented:
SELECT  inner_q.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as hbn_row_no FROM
 (SELECT a.c_action_id AS actionId, a.c_action_type_id AS actionType,
 a.c_isactive as isactive, (SELECT l.c_valuez FROM t_lookup_actions_lang  l
 WHERE a.c_action_id=l.c_action_id AND l.c_lang_id=1) as english,
 (SELECT l.c_valuez FROM t_lookup_actions_lang l WHERE a.c_action_id=l.c_action_id AND l.c_lang_id=2)
 as arabic  FROM t_lookup_actions a ) inner_q)

"Why the query aboverequired in the loop"
roy_sanudeveloper

Author

Commented:
Why there is no clarity on this issue as I still confused.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Distinguished Expert 2017
Commented:
I do not understand your question,
I do not have any information on your db.
You simingly have the same table used in two different ways ...

Breakdown each grouped select.

You are trying to define within the same multiple tables:English,Arabic, inner_q
The CTE is to define the data you are interested in
Then the second query uses that cte table to query for the conclusionary resolved.
roy_sanudeveloper

Author

Commented:
Thank you sir.i got it some thing from it, still I need to explore my understanding. By the what is CTE? by the way.Good day. Your writing gives me some bit of clarity, let me see if I can help myself

Thanks sir.
Regards R.S
Distinguished Expert 2017

Commented:
with cte, https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

What you can do is build one table while adding a column that says whether the language is English or Arabic

Look at using case evaluation
When   Expression     'Arabic'
When  expression1 'English'

Then your query would just join/ref, the CTE language table to get your end results.
roy_sanudeveloper

Author

Commented:
Thank you sir.😊 still I am analyzing
RS.😊

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial