@manhnguyenv

Welcome to my blog!

Using Stored Procedures and Parameters

April 22, 2019 22:52

Using Stored Procedures and Parameters (edit)

https://devio.wordpress.com/2009/01/23/generating-a-c-stored-procedure-wrapper-in-tsql/

select * from information_schema.parameters where specific_name = N'GetOrderDetails'

SELECT dbo.syscolumns.name, dbo.systypes.name, dbo.syscolumns.length
FROM dbo.sysobjects
INNER JOIN dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id
INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE (dbo.sysobjects.name = N'GetOrderDetails') AND (dbo.systypes.name <> N'sysname')
ORDER BY dbo.sysobjects.name, dbo.syscolumns.colid

Generate C# code such as stored procedure wrappers

Parses SQL files to create a meta-object hierarchy with which you can generate C# code such as stored procedure wrappers.

https://github.com/manhnguyenv/sqlsharpener

Run all stored procedures & view results in the temporary table

https://devio.wordpress.com/2009/11/26/checking-ms-sql-server-stored-procedures/

Good articles

https://devio.wordpress.com/2009/07/27/creating-a-light-weight-data-access-layer-in-c-1/

https://devio.wordpress.com/2009/07/28/creating-a-light-weight-data-access-layer-in-c-2/

https://devio.wordpress.com/2009/07/29/creating-a-light-weight-data-access-layer-in-c-3/

https://devio.wordpress.com/2009/07/30/creating-a-light-weight-data-access-layer-in-c-4/

https://devio.wordpress.com/2009/08/04/creating-a-light-weight-data-access-layer-in-c-5/

https://devio.wordpress.com/2009/09/09/creating-a-light-weight-data-access-layer-in-c-6/

https://devio.wordpress.com/2011/08/05/batch-insert-using-linq-to-sql/

Call Stored Procedure (C#)

https://blog.cloudboost.io/how-to-use-sql-output-parameters-in-stored-procedures-578e7c4ff188

https://www.aspsnippets.com/Articles/How-to-return-Output-parameter-from-Stored-Procedure-in-ASPNet-in-C-and-VBNet.aspx

Sử dụng Stored Procedure với PowerShell

http://www.dbascript.com/execute-sql-stored-procedure-from-powershell-with-parameters

Tăng tốc độ Stored Procedure với Table-Valued Parameters

https://visualstudiomagazine.com/articles/2015/06/01/table-valued-parameters.aspx

Stored Procedure WHILE

DECLARE @site_value INT;
SET @site_value = 0;

WHILE @site_value <= 10
BEGIN
   PRINT 'Inside WHILE LOOP on TechOnTheNet.com';
   SET @site_value = @site_value + 1;
END;

PRINT 'Done WHILE LOOP on TechOnTheNet.com';
GO

Stored Procedure CURSOR

DECLARE contacts_cursor CURSOR FOR
SELECT contact_id, website_id
FROM contacts;

OPEN contacts_cursor;
FETCH NEXT FROM contacts_cursor;

WHILE @@FETCH_STATUS = 0
   BEGIN
      FETCH NEXT FROM contacts_cursor;
      PRINT 'Inside WHILE LOOP on TechOnTheNet.com';
   END;
PRINT 'Done WHILE LOOP on TechOnTheNet.com';

CLOSE contacts_cursor;
DEALLOCATE contacts_cursor;
GO

Categories

Recent posts