Click here to Skip to main content
15,867,307 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to convert below SQL inline Query to Stored Procedure in MS SQL Server ?

SQL
Dataset sql_text = ISQL.ExecuteDataSet("SP_ParameterValAdmin");

sql_command = sql_text.Tables[0].Rows[0][0].ToString();

sql_command =
select DisplayName,reportcode
from mstreports
where reportgroupcode= + Convert(myData.Tables[0].Rows[i][1]) +  and active=1 + sql_command +
order by reportcode
Posted

It should looks like:
SQL
CREATE PROCEDURE TheNameOfProcedure
    @reportgroupcode <TypeOfData>,
    @AnotherCondition <TypeOfData>
AS
BEGIN
    SELECT DisplayName, reportcode
    FROM mstreports
    WHERE reportgroupcode= @reportgroupcode  AND active=1 AND @AnotherCondition
    ORDER BY reportcode
END

where:
<TypeOfData> means one of that: Data Types (T-SQL)[^]
@reposrtgroupcode comes from myData.Tables[0].Rows[i][1]
@AnotherCondition comes from sql_text.Tables[0].Rows[0][0].ToString()
 
Share this answer
 
You should make your query parameterized in order to make it proper SQL Store Procedure.

The SQL logic will be the same however you will be passing @variables(parameters) form your code to SQL.

Probably you should check this guidelines here, It will help you overall further for creating complex dynamic inline queries to sql store procedures.

Optimize-stored-procedures[^]

Hope this will help you.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900