December 15, 2021 at 4:21 pm
Hi
How to convert below code like -
CREATE PROCEDURE [dbo].[GetData]
(
@Parameter1 varchar(256),
@Parameter2 varchar(256),
@Parameter3 int = null
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@BaseQuery nvarchar(max) = N'SELECT T.* FROM dbo.Table1 AS T'
, @ParamList nvarchar(max) = N'@p1 varchar(256), @p2 varchar(256), @p3 int'
, @WhereClause nvarchar(max) = ' WHERE 1=1';
IF @Parameter1 IS NOT NULL
BEGIN
SET @WhereClause = @WhereClause + ' AND T.Url = @p1';
END
IF @Parameter2 IS NOT NULL
BEGIN
SET @WhereClause = @WhereClause + ' AND T.ID = @p2';
END
IF @Parameter3 IS NOT NULL
BEGIN
SET @WhereClause = @WhereClause + ' AND T.ID2 = @p3';
END
SET @BaseQuery = @BaseQuery + @WhereClause;
EXECUTE sp_executesql @BaseQuery, @ParamList, @p1 = @Parameter1, @p2 = @Parameter2, @p3 = @Parameter3;
END
-------------------------------
Alter PROCEDURE [dbo].[sp_test]
@frDate date,
@toDate date,
@VName nvarchar(100) = null,
@CName nvarchar(100) = null
as
begin
SELECT
T1.VendCode as "Vendor Code",T1.VendName as "Vendor Name",
T1.CustCode as "Cust Code",T1.CustName as "Cust Name",T1.ItemCode
from tbl1 T1 inner join tbl2 T2 on T0.Id = T1.Id
where T0.Pdate BETWEEN @frDate and @toDate
Thanks
December 15, 2021 at 5:43 pm
I am a bit confused why this is dynamic SQL and what you are trying to do. You have 2 stored procedures that appear to be unrelated. The first uses dynamic SQL (but a quick glance over it makes it seem like that is not needed), the second is just a select with 4 input parameters, 2 of which are in use.
What are you trying to convert? To non-dynamic SQL or to another language (Oracle from SQL Server for example) or from English to German or something else entirely?
This ALMOST looks like an interview question or a homework question. If that is the case, I don't mind pointing you in the right direction, but I'd much rather lead you to the answer than spoon feed you the solution. That being said, I am not even sure at the moment what the question is.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply