logo
down
shadow

SQL Server Compact stored procedure with multiple values in 1 input


SQL Server Compact stored procedure with multiple values in 1 input

Content Index :

SQL Server Compact stored procedure with multiple values in 1 input
Tag : sql-server , By : Pat
Date : November 24 2020, 01:01 AM

Does that help I'm using SQL Server Compact edition with the following stored procedure: , To split CSV to rows use this code
DECLARE @favs      VARCHAR(max)=',1,,253,,67,,85,,1034,,84565,,154,',
        @delimiter VARCHAR(10)=',,'

SELECT Rtrim(Ltrim(Split.a.value('.', 'Bigint'))) Result
FROM   (SELECT Cast ('<M>' + Replace(substring(@favs,2,len(@favs)-2), @delimiter, '</M><M>')
                     + '</M>' AS XML) AS Data) AS A
       CROSS APPLY Data.nodes ('/M') AS Split(a) 
Result
------
1
253
67
85
1034
84565
154
SELECT *
FROM   acResults
WHERE  acID IN (SELECT Rtrim(Ltrim(Split.a.value('.', 'Bigint')))
               FROM   (SELECT Cast ('<M>' + Replace(substring(@favs,2,len(@favs)-2), ',,', '</M><M>') + '</M>' AS XML) AS Data) AS A
                      CROSS APPLY Data.nodes ('/M') AS Split(a)) 
ORDER BY acUpdated
   OFFSET (@PageNumber-1)*@RowsPerPage ROWSFETCH NEXT @RowsPerPage ROWS ONLY
DECLARE @favs      VARCHAR(max)=',1,,253,,67,,85,,1034,,84565,,154,',
        @delimiter VARCHAR(10)=',,'

SELECT 'works'
WHERE  convert(bigint,253) IN(SELECT Rtrim(Ltrim(Split.a.value('.', 'varchar(200)'))) Result
              FROM   (SELECT Cast ('<M>' + Replace(substring(@favs,2,len(@favs)-2), @delimiter, '</M><M>')
                                   + '</M>' AS XML) AS Data) AS A
                     CROSS APPLY Data.nodes ('/M') AS Split(a)) 

Comments
No Comments Right Now !

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

Share : facebook icon twitter icon

Adding stored procedure in sql server compact, MVC4


Tag : sql-server , By : Chandra P Singh
Date : March 29 2020, 07:55 AM
Hope this helps SQL Server Compact doesn't support stored procedures.
See Differences Between SQL Server Compact and SQL Server

Passing array of values to SQL stored procedure in .net compact framework


Tag : chash , By : amy
Date : March 29 2020, 07:55 AM
this will help Im working on a windows app (windows CE) and I need to send an array of ID's to stored procedure (Stored in SQL server 2012). , If you need to pass only ID's then maybe this function will help you:
ALTER FUNCTION [dbo].[fn_SplitIds]
(
    @text nvarchar(max)
)
RETURNS @Ids TABLE
(
    Id int UNIQUE
) 
AS
BEGIN

declare     @iStart int,
            @iPos int
if substring( @text, 1, 1 ) = ',' 
begin
    set     @iStart = 2
    insert into @Ids
    values( null )
end
else 
    set     @iStart = 1
while 1=1
begin
    set     @iPos = charindex( ',', @text, @iStart )
    if @iPos = 0
            set     @iPos = len( @text )+1
    if @iPos - @iStart > 0                  
            insert into @Ids
            values  ( cast(substring( @text, @iStart, @iPos-@iStart ) as int))
    else
            insert into @Ids
            values( null )
    set     @iStart = @iPos+1
    if @iStart > len( @text ) 
            break
end

RETURN
END

How to convert stored procedure to SQL Server Compact Edition query


Tag : sql , By : pcoramc
Date : March 29 2020, 07:55 AM
this will help Here is the code that I used to determine the time value and dynamically specify the column name:
            float basalrate = 0;
        DateTime HourVal = Convert.ToDateTime(PumpEditTime);
        if (HourVal == null)
        {
            HourVal = DateTime.Now;
        }
        int hourval = HourVal.Hour;
        int ampmhour = Convert.ToInt32(HourVal.ToString("hh"));
        string hourampm = string.Empty;

        if (hourval == Convert.ToInt32("24"))
        {
            hourampm = "[12:00AM(00:00)]";
        }
        else if ((hourval > 12) && (hourval < 24))
        {
            hourampm = "[" + ampmhour + ":00PM(" + hourval + ":00)]";
        }
        else if (hourval == 12)
        {
            hourampm = "[12:00PM(12:00)]";
        }
        else if ((hourval < 12) && (hourval > 9))
        {
            hourampm = "[" + ampmhour + ":00AM(" + hourval + ":00)]";
        }
        else
        {
            hourampm = "[" + ampmhour + ":00AM(0" + hourval + ":00)]";
        }

Converting Stored Procedure into a query (SQL Server Compact)?


Tag : chash , By : Tom D
Date : March 29 2020, 07:55 AM
With these it helps You can use @@IDENTITY to return the last inserted identity value:
string queryString = "INSERT INTO Tests " + 
                  "([Date], [Device], [DeviceType], [ExecutionTimeMs]) " +
                  "VALUES (@Date, @DeviceSerialNumber,@DeviceType, @ElapsedTime); " + 
                  "SELECT @@IDENTITY;"
var newIdentity;
// set up the queryString variable & command using the above
newIdentity = cmd.ExecuteScalar();    

How to call MySQL stored procedure with multiple input values from ASP.NET


Tag : chash , By : Schmidt
Date : March 29 2020, 07:55 AM
Hope this helps You can add mutiple parameters in command.Parameters, refer the below code for the same.
        var connectionString = ""; // Provide connecction string here.
    using (var connection = new MySqlConnection(connectionString))
    {
        MySqlCommand command = new MySqlCommand("InsertIntotblStudent", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new MySqlParameter("PStudentId", TextBox1.Text));
        command.Parameters.Add(new MySqlParameter("PStudentName", TextBox2.Text));
        command.Connection.Open();
        var result = command.ExecuteNonQuery();
        command.Connection.Close();
    }
Related Posts Related QUESTIONS :
  • What is the difference between 'CREATE USER [user_name] FOR LOGIN [login_name]' and 'CREATE USER [user_name] FROM LOGIN
  • How to handle a subquery with no results?
  • Access linked to SQL Server shows #Name? in fields after filtering
  • What is different between the two methods of generating cluster primary keys?
  • How can I restore an SQL Server database when starting the Docker container?
  • How to call a SQL Server procedure with both input and output parameters?
  • SQL: Can this be written more efficiently?
  • Why are and are the same in SQL Server
  • date function will return different date if date changes while executing the query?
  • Creating a function for a snap, crackle, pop
  • Difference between SSDT in VS and Integration Services in SQL Server
  • Select all date between which an object has certain properties
  • Why does LEN( char(32) ) = 0 in T-SQL?
  • To split records in one column into multiple columns
  • Select cities from table, which do not start with vowels and do not end with vowels
  • Call stored procedure for each row from select
  • Inner Join Query with 2 tables giving different result compared to Sub Query for same 2 tables Northwind database
  • After a specific time of day - which method is better?
  • SQL Server: link DB2 table
  • Error copying SQL Server database from one server to another
  • Need to calculate the sum of second using a function from a string variable using SQL
  • How to convert from DD/mm/yyyy in varchar(max) to yyyymmdd format in SQL Server
  • How to remove only certain items within a column that has multiple words listed in cell
  • Partition or Index large table in SQL Server
  • Switch CASE in stored procedure in SQL
  • VS 2019 SSIS package can't add SQL Server
  • Combine two Column into One column with out union
  • is there a way to update multiple row using one update query?
  • How to we prevent SQL Injection by Regular Expression or any other way in asp.net
  • What does # after a column name mean in SQL Server
  • Using SSIS and T-SQL to convert date to dd.mm.yyyy
  • Stored procedure from VBA Excel not running
  • Stored procedure with dynamic SQL and ORDER BY
  • Join two tables in SQL Server
  • SQL Server substring(substring())
  • Msg 209, Level 16, State 1, Line 4 Ambiguous column name 'DonorID'. Msg 209, Level 16, State 1, Line 1 Ambiguous column
  • CASE WHEN Formula in SQL
  • T-SQL Issue using lead or lag
  • ALTER TABLE my_table ADD @column INT
  • sql server deadlock issue on a single table
  • Any tool to convert SQLite database to sql server?
  • Union and If Exists - not working together - Please help
  • SQL Server Index cost
  • How can I connect to SQL Server using (local)\INSTANCE_NAME?
  • How do I filter SQL Server traffic between app and DB servers using Wireshark?
  • How to protect my SQL Server database from unauthorized access, modification and distribution?
  • SQL Server 2008 Dead lock issue
  • writing an Dynamic query in sqlserver
  • When are SQL views appropriate in ASP.net MVC?
  • Upgrading From Sql Server 2005 to Sql Server 2008
  • Disable TSQL script check?
  • Building Stored Procedure to group data into ranges with roughly equal results in each bucket
  • Update table with index is too slow
  • Atomic UPSERT in SQL Server 2005
  • How to lock a table for inserting in sql?
  • How to run a ssis package using a sql statement?
  • using "in" with a case statement
  • Database schema last updated date
  • Fully automated SQL Server Restore
  • How to get identities of inserted data records using SQL bulk copy
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com