logo
down
shadow

How to pass a list of strings a sql parameter for the stored procedure to delete?


How to pass a list of strings a sql parameter for the stored procedure to delete?

Content Index :

How to pass a list of strings a sql parameter for the stored procedure to delete?
Tag : chash , By : Aki Björklund
Date : November 27 2020, 12:01 PM

hop of those help? As far as I can see you are passing list of names separated by comma and you want to delete all those names. You need to use IN operator to find all holiday names that should be deleted.
Here is an example how to do it for @SelectedHolidays:
declare @SelectedHolidays nvarchar(500) = 'H1,H2,H3'
declare @SelectedHolidaysXml xml = cast(replace(N'<R><I>' + @SelectedHolidays + N'</I></R>', ',', '</I><I>') as xml)


DELETE FROM [SessionHolidayMapping]
WHERE [HolidayName] in (select x.items.value('(.)[1]', 'NVARCHAR(500)') from @SelectedHolidaysXml.nodes('/R/I') as x(items))
AND [SessionId] = @SessionId

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

How to pass NULL or empty strings to stored procedure input parameter with ADO and VB?


Tag : sql , By : al.
Date : March 29 2020, 07:55 AM
I hope this helps you . A quick test here shows that's NULL ought to do the job. Sample code I used to test (onto a simple form with one button and one textbox):
Private Sub Command1_Click()
    Dim dbConn As ADODB.Connection
    Dim dbComm As ADODB.Command
    Dim dbRS As ADODB.Recordset

    Set dbConn = New ADODB.Connection
    With dbConn
        .ConnectionString = "...REPLACE THIS ACCORDINGLY..."
        .ConnectionTimeout = 10
        .Open
    End With
    Set dbComm = New ADODB.Command
    With dbComm
        .ActiveConnection = dbConn
        .CommandType = adCmdStoredProc
        .CommandText = "usp_Bob"
        .Parameters.Append .CreateParameter("b", adVarChar, adParamInput, 10, Null)
        Set dbRS = .Execute
    End With
    Text1.Text = dbRS.Fields.Item(0).Value

    dbRS.Close
    dbConn.Close
End Sub
ALTER PROCEDURE usp_Bob
 @b VARCHAR(10)
AS
 IF @b IS NULL
  SELECT 'NULL' AS '1'
 ELSE
  IF @b = ''
   SELECT 'EMPTY' AS '1'
  ELSE
   SELECT 'NOT NULL AND NOT EMPTY' AS '1'
IF @param = ''
    SET @param = NULL

how to pass parameter of stored procedure to the selected list?


Tag : mysql , By : David
Date : March 29 2020, 07:55 AM
should help you out You need to start the stored procedure body with a BEGIN.
Also I'd recommend against using @vars in a stored procedure, because they leak to the outside. Better to used typed declared vars.
DELIMITER $$

CREATE PROCEDURE SP_InsertTag_Level2 (IN tag_v varchar(50), IN CNT_v int)
BEGIN
  DECLARE tid INTEGER;
  DECLARE myid INTEGER;
  SELECT tweet_id INTO tid FROM tweet_tags WHERE tag=tag_v;
  INSERT INTO collected_tags (tag,country) 
     SELECT tag_v, A.country 
     FROM collected_tags A
     INNER JOIN tweet_tags B on (A.tag = B.tag)
     WHERE B.tweet_id = tid
  SELECT id INTO myId FROM collected_tags WHERE tag=tag_v;
  IF (myId IS NOT NULL) THEN
    INSERT INTO stats_tag(id,counter) VALUES (myid,CNT_v);
  END IF;
END $$

DELIMITER ;

Pass List of strings to a stored procedure


Tag : chash , By : Yst
Date : March 29 2020, 07:55 AM
like below fixes the issue Convert the comma seperated value to table using the XML. Use this updated procedure.
USE [App]
GO
/****** Object:  StoredProcedure [dbo].[GetWorkspaceMapDetailsForUserByGroups]    
     Script Date: 16/02/2015 10:37:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetWorkspaceMapDetailsForUserByGroups]   
    @workspaceID int,
    @viewMap nvarchar(256)
 AS 

SELECT 
  m.*
FROM 
  GeoAppMapDef m
WHERE
 m.workspaceID = @workspaceID
 and m.IsDeleted = 0
 and m.ViewMap IN 
 (
  SELECT 
     Split.a.value('.', 'VARCHAR(100)') AS CVS  
  FROM  
  (
    SELECT CAST ('<M>' + REPLACE(@viewMap, ',', '</M><M>') + '</M>' AS XML) AS CVS 
  ) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
)

How to pass list of items as parameter to a stored procedure


Tag : sql-server-2008 , By : user87225
Date : March 29 2020, 07:55 AM

How do I pass a list as a parameter in a stored procedure?


Tag : sql , By : Murali Ravipudi
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • Passing enum type to Converter with integer value
  • Pool of objects with objects that are already on the scene in advance
  • StatusBar text fade-out when binding using Caliburn.Micro
  • Queryfilter on ApplicationUser in OnModelCreating in ApplicationDbContext creates StackOverflowException
  • How to get record form a different table based on a value from first table with linq expression?
  • Show data in Grid from returned model
  • Using Attributes to Override Data Model Conventions
  • Basic OOP console calculator, result Error
  • Compositon and Repository pattern
  • Multiple using statements with if condition
  • How do i increase a number by 1 in every line that contain the number 1
  • Add binding to elements that are created in codebehind
  • How to add a column in an existing AspNetUsers table
  • Order a list of elements with another list of doubles
  • How to setup a NuGet package to copy content files to output build directory?
  • In SignalR Core using ChannelWriter: Do I need to call TryComplete twice if there's an exception?
  • C# GetProcessesByName: issue with colon
  • c# wpf | create complex object with user-defined name to Serialize into JSON
  • How can I get a instance of a generic list with reflection?
  • WPF XAML - Design time and visibility of textbox
  • EF Core and MySql query is too slow
  • Getting Registered App Display Name from an App Id
  • How to get all variables from a string
  • Delete entity with all childs connected
  • Azure Build agent cant´t find class library referance
  • Initialize Nested Dictionaries in c#
  • .Net Core Binding
  • Generic event test method, preventing code duplication
  • How do I keep the ellipses in the center when the screen is resized
  • How to require a property using JsonSchema.NET?
  • C# XDocument Element/Elements returns null
  • Autofac keyed service with IEnumerable relationship type
  • Installing EntityFramework via NuGet manager
  • Always Check if there is Internet Connection Xamarin forms
  • WCF OneWay service slows down when aspNetCompatibilityEnabled is set to false
  • Can we use JsonRequestBehavior.AllowGet with [HttpPost] attribute?
  • How to customize the Setup wizard with custom forms in Visual Studio setup project
  • C# ASP.NET - Use method from another class to create labels
  • C# List IList or IEnumerable as argument
  • Parsing File with C# And Replace method
  • Losing special unicode characters in encryption (C#)
  • Getting stored procedure returned value instead of row affected
  • How can I construct HTML using NameValuePair in android?
  • Loading a pop up page in ASP.net through a js file
  • How to pass alert or notification message from controller to View?
  • C# to pause, turn on ssas server, backup cube.... how to?
  • How to execute DataTable.Select() for a column of custom class type for a particular element in that C#
  • how to connect mysql8.0 with C#
  • Passing incorrect values into MultiValueConverter by MultiBinding
  • Can i use IEnumerator as Update func?
  • How to convert API Json response to C# Array?
  • Blazor Textfield Oninput User Typing Delay
  • Performing both layout and render transform results in wrong output
  • uwp beforetextchanged cursor moving in front of text
  • How to keep duplicates from a string[] exclude words from a List and print them out
  • .Net Core Strings.Asc/Mid/Chr/Len missing even after importing Microsoft.VisualBasic
  • How to return to previous search page without being asked to Confirm Form Re-submission and keeping the results on ASP.N
  • How set a identity scaffolding item/page how initial page in asp.net MVC core?
  • LINQ isn't calling Dispose on my IEnumerator when using Union and Select, expected behavior or bug?
  • What is "ByteArray.uncompress()" in AS3 equivalent to in C#?
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com