Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    15

    Unanswered: run a SQLserver stored procedure from C

    Hello all,

    how do I run a stored procedure called sp_tag in a server using sqlsever, by C language.

    thx.

  2. #2
    Join Date
    Jun 2007
    Posts
    3
    //================================================== ===================
    //
    // File: ProcessReturnCodes.cpp
    // Summary: The sample shows processing a return code and output parameter.
    //
    // This application assumes the existence of the following stored procedure:
    //
    // use AdventureWorks
    // DROP PROCEDURE TestParm
    // GO
    //
    // CREATE PROCEDURE TestParm
    // @OutParm int OUTPUT
    // AS
    // SELECT Name FROM Purchasing.Vendor
    // SELECT @OutParm = 88
    // RETURN 99
    // go
    //
    // Date: December 23, 2005
    //
    //---------------------------------------------------------------------
    //
    // This file is part of the Microsoft SQL Server Code Samples.
    // Copyright (C) Microsoft Corporation. All rights reserved.
    //
    //This source code is intended only as a supplement to Microsoft
    //Development Tools and/or on-line documentation. See these other
    //materials for detailed information regarding Microsoft code samples.
    //
    //THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
    //ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
    //THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
    //PARTICULAR PURPOSE.
    //
    //================================================== ===================

    #include <stdio.h>
    #include <string.h>
    #include <windows.h>
    #include <sql.h>
    #include <sqlext.h>
    #include <odbcss.h>

    #define MAXBUFLEN 255

    SQLHENV henv = SQL_NULL_HENV;
    SQLHDBC hdbc1 = SQL_NULL_HDBC;
    SQLHSTMT hstmt1 = SQL_NULL_HSTMT;

    void Cleanup() {
    if (hstmt1 != SQL_NULL_HSTMT)
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);

    if (hdbc1 != SQL_NULL_HDBC) {
    SQLDisconnect(hdbc1);
    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
    }

    if (henv != SQL_NULL_HENV)
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
    }

    int main() {
    RETCODE retcode;
    // SQLBindParameter variables.
    SWORD sParm1 = 0, sParm2 = 1;
    SQLLEN cbParm1 = SQL_NTS;
    SQLLEN cbParm2 = SQL_NTS;

    // Allocate the ODBC environment and save handle.
    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
    if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
    printf("SQLAllocHandle(Env) Failed\n\n");
    Cleanup();
    return(9);
    }

    // Notify ODBC that this is an ODBC 3.0 app.
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
    if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
    printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
    Cleanup();
    return(9);
    }

    // Allocate ODBC connection handle and connect.
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
    if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
    printf("SQLAllocHandle(hdbc1) Failed\n\n");
    Cleanup();
    return(9);
    }

    // This sample use Integrated Security. Create the SQL Server DSN by using the Windows NT authentication.
    retcode = SQLConnect(hdbc1, (UCHAR*)"AdventureWorks", SQL_NTS, (UCHAR*)"",SQL_NTS, (UCHAR*)"", SQL_NTS);

    if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
    printf("SQLConnect() Failed\n\n");
    Cleanup();
    return(9);
    }

    // Allocate statement handle.
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
    if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
    printf("SQLAllocHandle(hstmt1) Failed\n\n");
    Cleanup();
    return(9);
    }

    // Bind the return code to variable sParm1.
    retcode = SQLBindParameter(hstmt1, 1, SQL_PARAM_OUTPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &sParm1, 0, &cbParm1);
    if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
    printf("SQLBindParameter(sParm1) Failed\n\n");
    Cleanup();
    return(9);
    }

    // Bind the output parameter to variable sParm2.
    retcode = SQLBindParameter(hstmt1, 2, SQL_PARAM_OUTPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &sParm2, 0, &cbParm2);
    if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
    printf("SQLBindParameter(sParm2) Failed\n\n");
    Cleanup();
    return(9);
    }

    // Execute the command.
    retcode = SQLExecDirect(hstmt1, (UCHAR*)"{? = call TestParm(?)}", SQL_NTS);
    if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
    printf("SQLExecDirect Failed\n\n");
    Cleanup();
    return(9);
    }

    // Show parameters are not filled.
    printf("Before result sets cleared: RetCode = %d, OutParm = %d.\n", sParm1, sParm2);

    // Clear any result sets generated.
    while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA )
    ;

    // Show parameters are now filled.
    printf("After result sets drained: RetCode = %d, OutParm = %d.\n", sParm1, sParm2);

    // Clean up.
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
    SQLDisconnect(hdbc1);
    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
    }

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •