Solved

sql server update procedure

Posted on 2016-07-27
6
39 Views
Last Modified: 2016-07-27
I dont know the syntax, I have this so far I think you can see what I want to do.

USE [Dictionary]
GO

CREATE PROCEDURE [dbo].[usp_UpdateShortStrings]
(
@ClientCodeIn nvarchar(50) = NULL,
@StrShortUnTagged nvarchar(Max) = NULL,
@StrShortTagged nvarchar(Max) = NULL
)
AS
BEGIN
UPDATE dbo.TblWords
SET StrShort = @StrShortUnTagged,
SET StrShortTagged = @StrShortTagged 
WHERE ClientCode = @ClientCodeIn
END

Open in new window

0
Comment
Question by:PeterBaileyUk
  • 4
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41731348
>I have this so far I think you can see what I want to do.
The syntax looks correct, and I hate to guess what users' requirements are, so why don't you tell us what you're trying to pull off here, and any error messages that are displayed when you execute the above code.
0
 

Author Comment

by:PeterBaileyUk
ID: 41731366
as it was just to understand the syntax for an update procedure I didnt expand, I will pass the client code, and the other two variables in vb.net app and it update the word table.

The vb code is here but I havnt made the call to the procedure yet. so a bit of a mute point.

the code creates the two strings i want to send to the table words

   Public Sub CreateShort()
        Dim dtCodes = New DataTable
        Dim dtCodeWords = New DataTable
        Dim dtCodeWordsT = New DataTable
        Dim cmd As New SqlCommand
        Dim CmdWords As New SqlCommand
        Dim StrClientCode As String
        Dim StrProcName As String
        Dim StrShortString As String
        Dim StrShortTaggedString As String

        StrProcName = "usp_getUnTaggedCodeWords"
        Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")

            cnSql.Open()
            cmd.Connection = cnSql
            cmd.CommandText = "SELECT TblWords.ClientCode FROM TblWords LEFT JOIN TblWordTags ON TblWords.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition WHERE (((TblWordTags.ClientCodeWordPosition) Is Not Null)) GROUP BY clientcode;"

            Dim reader As SqlDataReader = cmd.ExecuteReader()
            dtCodes.Load(reader)

            For Each drRecord As DataRow In dtCodes.Rows

                StrClientCode = drRecord("ClientCode")
                If StrClientCode = "069558C" Then
                    MessageBox.Show("now")
                End If

                dtCodeWords = GetShortStringWordsData(StrClientCode, False)

                For Each drRecordW As DataRow In dtCodeWords.Rows
                    StrShortString = StrShortString & drRecordW("word") & Chr(32)
                    StrShortString = RTrim(StrShortString)
                    Debug.Print(StrShortString)
                Next

                dtCodeWordsT = GetShortStringWordsData(StrClientCode, True)

                For Each drRecordWT As DataRow In dtCodeWordsT.Rows
                    StrShortTaggedString = StrShortTaggedString & drRecordWT("word") & Chr(32)
                    StrShortTaggedString = RTrim(StrShortTaggedString)
                    Debug.Print(StrShortTaggedString)
                Next







            Next


        End Using
    End Sub

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41731373
ive attached the screenshot form sql server it has red underlines so the syntax must be wrong
ee.JPG
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41731379
Second thought, I see the error, the second SET statement should not be there.  In a single UPDATE with multiple columns the columns are separated only by commas.

USE [Dictionary]
GO

CREATE PROCEDURE [dbo].[usp_UpdateShortStrings]
(
@ClientCodeIn nvarchar(50) = NULL,
@StrShortUnTagged nvarchar(Max) = NULL,
@StrShortTagged nvarchar(Max) = NULL
)
AS
BEGIN
UPDATE dbo.TblWords
SET StrShort = @StrShortUnTagged,  StrShortTagged = @StrShortTagged  -- No SET before the second column
WHERE ClientCode = @ClientCodeIn
END
GO

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41731394
ok I worked it out myself;
USE [Dictionary]
GO

CREATE PROCEDURE [dbo].[usp_UpdateShortStrings]

@ClientCodeIn nvarchar(50) = NULL,
@StrShortUnTagged nvarchar(Max) = NULL,
@StrShortTagged nvarchar(Max) = NULL

AS
BEGIN
UPDATE dbo.TblWords
SET StrShort = @StrShortUnTagged, StrShortTag = @StrShortTagged 
WHERE ClientCode = @ClientCodeIn
END

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41731395
Thank you I experimented before i saw your answer, its fine now thx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question